SQL Server 相当于 Oracle 的 NULLS FIRST？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1456653/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server equivalent to Oracle's NULLS FIRST?
提问by OMG Ponies
So Oracle has NULLS FIRST, which I can use to have null values sorted at the top followed by my column value in descending order:
所以 Oracle 有 NULLS FIRST，我可以用它来将空值排在顶部，然后按降序排列我的列值：
ORDER BY date_sent NULLS FIRST
What is comparable in SQL Server? There are these alternatives, assuming the date values are NULL or in the past:
SQL Server 中的可比性是什么？假设日期值为 NULL 或过去，有这些替代方案：
ORDER BY ISNULL(date_sent, GETDATE()) DESC
ORDER BY (CASE WHEN t.setinactive IS NULL THEN 1 ELSE 2 END), t.setinactive DESC
ORDER BY -CAST(date_sent as int) ASC
Any others?
还有其他人吗？
回答by Lukasz Lysik
You can do some trick:
你可以做一些技巧：
ORDER BY (CASE WHEN [Order] IS NULL THEN 0 ELSE 1 END), [Order]
回答by JotaBe
The quick answer is this: the best solution for changing the ordering of nulls in the necessary cases is the accepted one. But you only have to use it, or a variation of it in the necessary cases:
快速回答是这样的：在必要情况下更改空值顺序的最佳解决方案是公认的解决方案。但是你只需要使用它，或者在必要的情况下使用它的变体：
DESC + NULLS FIRST:
ORDER BY (CASE WHEN [Order] IS NULL THEN 0 ELSE 1 END), [Order] DESCASC + NULLS LAST:
ORDER BY (CASE WHEN [Order] IS NULL THEN 1 ELSE 0 END), [Order] ASCASC + NULLS FIRST: it works fine by default
DESC + NULLS LAST: it works fine by default
首先是 DESC + NULL：
ORDER BY (CASE WHEN [Order] IS NULL THEN 0 ELSE 1 END), [Order] DESCASC + NULLS 最后：
ORDER BY (CASE WHEN [Order] IS NULL THEN 1 ELSE 0 END), [Order] ASCASC + NULLS FIRST：默认情况下它工作正常
DESC + NULLS LAST：默认情况下它工作正常
Let's see why:
让我们看看为什么：
If you check the ORDER BY Clause (Transact-SQL) MSDN docs, and scroll down to ASC | DESC, you can read this:
如果您检查ORDER BY Clause (Transact-SQL) MSDN docs并向下滚动到ASC | DESC，您可以阅读以下内容：
ASC | DESC
Specifies that the values in the specified column should be sorted in ascending or descending order. ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort order. Null values are treated as the lowest possible values.
ASC | 发展研究中心
指定指定列中的值应按升序或降序排序。ASC 从最低值到最高值排序。DESC 从最高值到最低值排序。ASC 是默认的排序顺序。空值被视为可能的最低值。
So, by default if you specify ASCorder, it works like NULLS FIRST. And, if you specify DESC, it works like NULLS LAST.
因此，默认情况下，如果您指定ASCorder，它的工作方式类似于NULLS FIRST. 而且，如果您指定DESC，它的工作原理就像NULLS LAST。
So you only need to do change the behavior for NULLS FIRSTin DESCorder, and for NULLS LASTin ASCorder.
所以，你只需要做改变行为NULLS FIRST的DESC顺序，并NULLS LAST在ASC顺序。
IMHO, the best solution for changing the ordering of nulls in the necessary cases is the accepted one, but I've included it adapted to the different cases in the beginning of my answer.
恕我直言，在必要的情况下更改空值顺序的最佳解决方案是已接受的解决方案，但我在回答的开头已将其包含在不同的情况下。
回答by Novitzky
Use Case/When statement, for example:
Use Case/When 语句，例如：
ORDER BY (case WHEN ColINT IS NULL THEN {maxIntValue} ELSE ColINT END) DESC
ORDER BY (case WHEN ColVChar IS NULL THEN {maxVCharValue} ELSE ColVChar END) DESC
ORDER BY (case WHEN ColDateT IS NULL THEN {maxDateTValue} ELSE ColDateT END) DESC
...and so on.
...等等。
or even better as you don't care what is your column type and the max value.
甚至更好，因为您不关心您的列类型和最大值是什么。
ORDER BY (case WHEN ColAnyType IS NULL THEN 1 ELSE 0 END) DESC, ColAnyType DESC
回答by Jonatas Sellos
A simple example:
一个简单的例子：
SELECT (CASE WHEN Value1 IS NULL THEN 1 ELSE 0 END) AS ValueIsNull, Value1, Value2, Value3
FROM TableName
ORDER BY ValueIsNull DESC, Value1
回答by George Mastros
If you have rows in your table with dates less than now, and other rows with dates greater than now, your NULLS would appear in the middle of the list. Instead, you should probably use a value that will never sort in the middle of your list.
如果表中的行日期小于现在，而其他行的日期大于现在，则 NULL 将出现在列表的中间。相反，您应该使用一个永远不会排在列表中间的值。
Order by IsNull(Date_Sent, '17530101') desc
按 IsNull(Date_Sent, '17530101') desc 排序
Note: That date is actually Jan 1, 1753.
注意：该日期实际上是 1753 年 1 月 1 日。
回答by JosephStyons
ORDER BY
COALESCE(POSTING_DATE,'1900-01-01 00:00:00.000')
,OTHER_FIELDS
回答by PaulG
This is an alternative way when you want to adjust how nulls appear in the sort order. Negate the column and reverse your sort order. Unfortunately you would need to CAST dateTime columns.
当您想要调整空值在排序顺序中的显示方式时，这是另一种方法。否定该列并反转您的排序顺序。不幸的是，您需要 CAST dateTime 列。
ORDER BY -CAST(date_sent as int) ASC
回答by David Andres
You can't control this, to my knowledge. And it looks like you have the correct approach with ISNULL.
据我所知，你无法控制这一点。看起来您对ISNULL.
With strings, I've used ISNULL(field, '')for the same purpose.
对于字符串，我也用于ISNULL(field, '')相同的目的。
