排序升序时0到最后

I am trying to select from one table a list of products ordered by price, year, name, and others.... The problem is that I must make zero values come last when sorting ascending.

我试图从一张表中选择按价格，年份，名称和其他订购的产品列表....问题是我必须在排序升序时将零值设为最后。

My code is:

我的代码是：

SELECT * FROM Product P 
ORDER BY  CASE WHEN @OrderBy='Date ASC' THEN Date  END ASC,
          CASE WHEN @OrderBy='Price ASC' THEN Price  END ASC,
          CASE WHEN @OrderBy='Title ASC' THEN Title  END ASC,
          CASE WHEN @OrderBy='' THEN Match END

This works but don't put the zero at the bottom of the list. So, I tried to transform it (see next code), but it gave me the error Incorrect syntax near ','.

这有效，但不要将零置于列表的底部。所以，我试图对它进行转换（参见下一个代码），但它在'，'附近给了我错误语法错误。

SELECT * FROM Product P
ORDER BY CASE WHEN @OrderBy='Price ASC' THEN 

           (case A.Price WHEN  0 THEN 1 ELSE 0  END,A.Price )

END ASC

I appreciate any help

我感谢任何帮助

6 个解决方案

#1

You can do it by testing for price-ordering twice:

您可以通过测试两次价格订购来实现：

SELECT * FROM Product P 
ORDER BY  CASE WHEN @OrderBy='Date ASC' THEN Date  END ASC,
          CASE WHEN @OrderBy='Price ASC' THEN CASE WHEN Price = 0 THEN 1 ELSE 0 END ASC,
          CASE WHEN @OrderBy='Price ASC' THEN Price END ASC,
          CASE WHEN @OrderBy='Title ASC' THEN Title  END ASC,
          CASE WHEN @OrderBy='' THEN Match END

By the way, the implicit value of the case expression when @orderBy doesn't equal the string is null. When the sort column contains all nulls, it effectively disables sorting for that attribute.

顺便说一句，当@orderBy不等于字符串时，case表达式的隐式值为null。当排序列包含所有空值时，它会有效地禁用该属性的排序。

#2

I would suggest using a large dummy price:

我建议使用大的虚拟价格：

ORDER BY CASE WHEN @OrderBy='Price ASC' THEN 99999999 ELSE A.Price END ASC

or if you DBMS supports NULLS LAST:

或者如果您的DBMS支持NULLS LAST：

ORDER BY CASE WHEN @OrderBy='Price ASC' THEN NULLIF(A.Price,0) END ASC NULLS LAST

#3

You can try with this syntax:

您可以尝试使用以下语法：

SELECT *,
    CASE WHEN @OrderBy = 'Price ASC' AND Price = 0 THEN 1 ELSE 0 END AS OrderPriceZeroLast
FROM Product P 
ORDER BY OrderPriceZeroLast,
    CASE WHEN @OrderBy = 'Date ASC' THEN Date END ASC,
    CASE WHEN @OrderBy = 'Price ASC' THEN Price END ASC,
    CASE WHEN @OrderBy = 'Title ASC' THEN Title END ASC,
    CASE WHEN @OrderBy = '' THEN Match END

#4

I can't add comments yet. There is bug in your code

我还不能添加评论。您的代码中存在错误

SELECT * FROM Product P
 ORDER BY CASE WHEN @OrderBy='Price ASC' THEN 
           (case A.Price WHEN  0 THEN 1 ELSE 0  END,A.Price )
END ASC

Shoud be smth like

应该像是一样

SELECT * 
FROM Product P
ORDER BY 
CASE 
  WHEN @OrderBy='Price ASC' 
  THEN 
    CASE A.Price 
      WHEN  0 
      THEN 1 
      ELSE 0  
      END  
END,
A.Price

#5

This may seem like a hack but you could create a new column on the result set on the fly. Like this:

这可能看起来像是一个黑客，但您可以动态地在结果集上创建一个新列。喜欢这个：

SELECT *, [name of column that may contain zeroes] as foo WHERE /* rest of your code */

SELECT *，[可能包含零的列的名称]为foo WHERE / *其余代码* /

Then you could sort DESC on foo and ASC on the rest. Just remember to now show foo to the user. Also notice that yes, you'll get the same column in the result set twice. You also have to use a CASE to turn all non-zero values into 1 (or some other constant value).

然后你可以在foo和ASC上对DESC进行排序。只记得现在向用户显示foo。另请注意，是的，您将在结果集中获得两次相同的列。您还必须使用CASE将所有非零值转换为1（或其他一些常量值）。

#6

Try this to sort 0 value to last, when there are 0,1,2,... values are in field. It will bring 1, 2 ,... and 0 to last order.

尝试将0值排序到最后，当有0,1,2时，...值在字段中。它将为最后一个订单带来1,2，...和0。

select * from Product 
order by 
case 
when OrderBy = 0 
    then -1 
else 0 
    end, 
OrderBy desc

-Chirag

#1