如何按发货日期升序对订单进行排序，但最后排序为NULL。 [英] how to sort the orders by shipped date ascending, but have NULLs sort last.

问题描述

例如，以下查询为每个订单返回订单ID和发货日期，

由后者订购。



SELECT orderid，shippingdate

FROM Sales.Orders

WHERE custid = 20

ORDER BY shippingdate;

请记住未发货订单在shippingdate列中为NULL;因此，它们在发货订单之前按照b $ b排序，如查询输出所示。

orderid shippingdate

---------- - -----------------------

11008 NULL

11072 NULL

10258 2006-07-23 00：00：00.000

10263 2006-07-31 00：00：00.000

10351 2006-11-20 00:00： 00.000

...

标准SQL支持选项NULLS FIRST和NULLS LAST来控制NULL如何排序，但是T-SQL没有' t支持此选项。作为一个有趣的挑战，看看你是否可以想象如何按发货日期升序对订单进行排序，但最后排序为NULL。 （提示：你

可以在ORDER BY子句中指定表达式;想想如何使用CASE表达式来实现这个任务。）

这是我到目前为止



使用TSQL2012

选择orderid，发货日期
来自sales.orders的


其中custid = 20

按个案订购

当shippingdate为null然后发货结束时desc;

什么是我错过了



谢谢

As an example, the following query returns for each order the order ID and shipped date,
ordered by the latter.

SELECT orderid, shippeddate
FROM Sales.Orders
WHERE custid = 20
ORDER BY shippeddate;
Remember that unshipped orders have a NULL in the shippeddate column; hence, they
sort before shipped orders, as the query output shows.
orderid shippeddate
----------- -----------------------
11008 NULL
11072 NULL
10258 2006-07-23 00:00:00.000
10263 2006-07-31 00:00:00.000
10351 2006-11-20 00:00:00.000
...
Standard SQL supports the options NULLS FIRST and NULLS LAST to control how NULLs
sort, but T-SQL doesn’t support this option. As an interesting challenge, see if you can figure
out how to sort the orders by shipped date ascending, but have NULLs sort last. (Hint: You
can specify expressions in the ORDER BY clause; think of how to use the CASE expression to
achieve this task.)
This is what i have so far

use TSQL2012
select orderid, shippeddate
from sales.orders
where custid = 20
order by case
when shippeddate is null then shippeddate end desc;
What am i missing

Thanks

推荐答案

检查这个

check this

use TSQL2012
select orderid, shippeddate
from sales.orders
where custid = 20
order by case
when shippeddate is null  then 1 else 0 end, shippeddate ;

select orderid, shippeddate
from sales.orders
where custid = 20
order by 
	CASE
		WHEN shippeddate IS NULL  THEN shippeddate 
		ELSE 0 
	END
		 DESC, shippeddate ASC;

这篇关于如何按发货日期升序对订单进行排序，但最后排序为NULL。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！