SQL Server相当于Oracle的NULLS ?

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，我可以使用它在顶部排序null值，然后用降序排序列值:

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?

还有其他的吗?

8 个解决方案

#1

You can do some trick:

你可以做一些魔术:

ORDER BY (CASE WHEN [Order] IS NULL THEN 0 ELSE 1 END), [Order]

#2

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:

快速的答案是:在必要的情况下更改nulls顺序的最佳解决方案是被接受的解决方案。但是你只需要使用它，或者在必要的情况下它的变化:

DESC + NULLS FIRST:

DESC + null第一:

ORDER BY (CASE WHEN [Order] IS NULL THEN 0 ELSE 1 END), [Order] DESC

按(如果[ORDER]为空，则0 ELSE 1结束)，[ORDER] DESC
ASC + NULLS LAST:

ASC + null最后:

ORDER BY (CASE WHEN [Order] IS NULL THEN 1 ELSE 0 END), [Order] ASC

ORDER BY (CASE WHEN [ORDER]为NULL，然后是另一个0)，[ORDER] ASC。
ASC + NULLS FIRST: it works fine by default

ASC + NULLS:默认情况下可以正常工作
DESC + NULLS LAST: it works fine by default

DESC + NULLS最后:默认情况下可以正常工作

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子句(Transact-SQL) MSDN文档，并向下滚动到ASC | DESC，您可以阅读以下内容:

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从最低的价值到最高的价值。DESC从最大值到最小值进行排序。ASC是默认的排序顺序。空值被视为可能的最低值。

So, by default if you specify ASC order, it works like NULLS FIRST. And, if you specify DESC, it works like NULLS LAST.

因此，默认情况下，如果指定ASC顺序，它的工作方式首先是NULLS。如果指定DESC，它的工作方式就像NULLS。

So you only need to do change the behavior for NULLS FIRST in DESC order, and for NULLS LAST in ASC order.

因此，您只需要在DESC顺序中更改NULLS的行为，并在ASC顺序中更改NULLS的行为。

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.

IMHO，在必要的情况下更改nulls顺序的最佳解决方案是被接受的，但是在我的答案的开头，我已经将它包含到不同的情况中。

#3

Use Case/When statement, for example:

用例/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

#4

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日。

#5

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

#6

ORDER BY
  COALESCE(POSTING_DATE,'1900-01-01 00:00:00.000')
 ,OTHER_FIELDS

#7

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.

当您想要调整null在排序顺序中的显示方式时，这是另一种方法。否定列并反转排序顺序。不幸的是，您需要强制转换dateTime列。

ORDER BY -CAST(date_sent as int) ASC

#8

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(字段，")来实现相同的目的。

#1