SQL Server：使用ORDER BY子句对NULL标记进行排序

I have this query :

我有这个问题：

SELECT orderid, shippeddate
FROM Sales.Orders
WHERE custid = 20
ORDER BY shippedate;

and the output is:

输出是：

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
10368   2006-12-02 00:00:00.000
...

As an exercise, I am trying to figure out how to sort the orders by shippeddate ascending, but have NULLs sort last.

作为练习，我试图弄清楚如何通过shippingdate升序对订单进行排序，但最后排序为NULL。

I know that standard SQL support the options NULL FIRST and NULL LAST, but T-SQL doesn't support this option.

我知道标准SQL支持NULL FIRST和NULL LAST选项，但T-SQL不支持此选项。

Any suggestions?

有什么建议么？

Thanks

谢谢

2 个解决方案

#1

You can do something like this:

你可以这样做：

SELECT orderid, shippeddate
FROM Sales.Orders
WHERE custid = 20
ORDER BY case when shippeddate is null then 2 else 1 end, shippedate;

#2

In addition,

此外，

You could replace value in the field if it is null by using ISNULL

如果使用ISNULL为null，则可以替换字段中的值

ISNULL ( check_expression , replacement_value )

So your query would look something like this

所以你的查询看起来像这样

SELECT ISNULL(orderid, '1900-01-01'), 
       shippeddate
FROM Sales.Orders
WHERE custid = 20 
ORDER BY shippedate asc;

So this would replace any NULLs in that field with a value (1900-01-01 in this case) you could sort on

因此，这将使用您可以排序的值（在本例中为1900-01-01）替换该字段中的任何NULL

#1