SQL SQL如何在升序排序时使空值排在最后
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1498648/
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 how to make null values come last when sorting ascending
提问by David Bo?jak
I have a SQL table with a datetime field. The field in question can be null. I have a query and I want the results sorted ascendingly by the datetime field, however I want rows where the datetime field is null at the end of the list, not at the beginning.
我有一个带有日期时间字段的 SQL 表。有问题的字段可以为空。我有一个查询，我希望结果按日期时间字段升序排序，但是我希望日期时间字段在列表末尾而不是开头的行为空。
Is there a simple way to accomplish that?
有没有简单的方法来实现这一点？
回答by RedFilter
select MyDate
from MyTable
order by case when MyDate is null then 1 else 0 end, MyDate
回答by a_horse_with_no_name
(A "bit" late, but this hasn't been mentioned at all)
（“有点”晚了，但根本没有提到这一点）
You didn't specify your DBMS.
您没有指定您的 DBMS。
In standard SQL (and most modern DBMS like Oracle, PostgreSQL, DB2, Firebird, Apache Derby, HSQLDB and H2) you can specify NULLS LASTor NULLS FIRST:
在标准 SQL（以及大多数现代 DBMS，如 Oracle、PostgreSQL、DB2、Firebird、Apache Derby、HSQLDB 和 H2）中，您可以指定NULLS LAST或NULLS FIRST：
Use NULLS LASTto sort them to the end:
用于NULLS LAST将它们排序到最后：
select *
from some_table
order by some_column DESC NULLS LAST
回答by Luksurious
I also just stumbled across this and the following seems to do the trick for me, on MySQL and PostgreSQL:
我也只是偶然发现了这一点，以下内容似乎对我有用，在 MySQL 和 PostgreSQL 上：
ORDER BY date IS NULL, date DESC
as found at https://stackoverflow.com/a/7055259/496209
回答by Gratzy
order by coalesce(date-time-field,large date in future)
回答by Majdi M. Aburahelah
You can use the built-in function to check for null or not null, as below. I test it and its working fine.
您可以使用内置函数来检查是否为空，如下所示。我测试它并且它工作正常。
select MyDate from MyTable order by ISNULL(MyDate,1) DESC, MyDate ASC;
select MyDate from MyTable order by ISNULL(MyDate,1) DESC, MyDate ASC;
回答by infogulch
If your engine allows ORDER BY x IS NULL, xor ORDER BY x NULLS LASTuse that. But if it doesn't these might help:
如果您的引擎允许ORDER BY x IS NULL, x或ORDER BY x NULLS LAST使用它。但如果没有，这些可能会有所帮助：
If you're sorting by a numeric type you can do this: (Borrowing the schema from another answer.)
如果您按数字类型排序，则可以执行以下操作：（从另一个答案中借用架构。）
SELECT *
FROM Employees
ORDER BY ISNULL(DepartmentId*0,1), DepartmentId;
Any non-null number becomes 0, and nulls become 1, which sorts nulls last.
任何非空数变为 0，空数变为 1，最后对空值进行排序。
You can also do this for strings:
您也可以对字符串执行此操作：
SELECT *
FROM Employees
ORDER BY ISNULL(LEFT(LastName,0),'a'), LastName
Because 'a'> ''.
因为'a'> ''。
This even works with dates by coercing to a nullable int and using the method for ints above:
这甚至可以通过强制为可为空的 int 并使用上面的 int 方法来处理日期：
SELECT *
FROM Employees
ORDER BY ISNULL(CONVERT(INT, HireDate)*0, 1), HireDate
(Lets pretend the schema has HireDate.)
（让我们假设架构有 HireDate。）
These methods avoid the issue of having to come up with or manage a "maximum" value of every type or fix queries if the data type (and the maximum) changes (both issues that other ISNULL solutions suffer). Plus they're much shorter than a CASE.
这些方法避免了必须提出或管理每种类型的“最大值”值或在数据类型（和最大值）更改时修复查询的问题（其他 ISNULL 解决方案都会遇到这两个问题）。此外，它们比 CASE 短得多。
回答by Luizgrs
When your order column is numeric (like a rank) you can multiply it by -1 and then order descending. It will keep the order you're expecing but put NULL last.
当您的订单列是数字（如排名）时，您可以将其乘以 -1，然后降序排列。它将保持您期望的顺序，但将 NULL 放在最后。
select *
from table
order by -rank desc
回答by user3923117
回答by Kasim Husaini
Thanks RedFilter for providing excellent solution to the bugging issue of sorting nullable datetime field.
感谢 RedFilter 为可空日期时间字段排序的漏洞问题提供了出色的解决方案。
I am using SQL Server database for my project.
我正在为我的项目使用 SQL Server 数据库。
Changing the datetime null value to '1' does solves the problem of sorting for datetime datatype column. However if we have column with other than datetime datatype then it fails to handle.
将 datetime 空值更改为 '1' 确实解决了 datetime 数据类型列的排序问题。但是，如果我们有日期时间数据类型以外的列，则无法处理。
To handle a varchar column sort, I tried using 'ZZZZZZZ' as I knew the column does not have values beginning with 'Z'. It worked as expected.
为了处理 varchar 列排序，我尝试使用“ZZZZZZZ”，因为我知道该列没有以“Z”开头的值。它按预期工作。
On the same lines, I used max values +1 for int and other data types to get the sort as expected. This also gave me the results as were required.
在同一行上，我对 int 和其他数据类型使用最大值 +1 来按预期进行排序。这也给了我所需的结果。
However, it would always be ideal to get something easier in the database engine itself that could do something like:
但是，在数据库引擎本身中获得一些更容易的东西总是很理想的，这些东西可以做这样的事情：
Order by Col1 Asc Nulls Last, Col2 Asc Nulls First
As mentioned in the answer provided by a_horse_with_no_name.
如a_horse_with_no_name 提供的答案中所述。
回答by 3limin4t0r
If you're using MariaDB, they mention the following in the NULL Values documentation.
如果您使用的是 MariaDB，他们会在NULL 值文档 中提到以下内容。
Ordering
When you order by a field that may contain NULL values, any NULLs are considered to have the lowest value. So ordering in DESC order will see the NULLs appearing last. To force NULLs to be regarded as highest values, one can add another column which has a higher value when the main field is NULL. Example:
SELECT col1 FROM tab ORDER BY ISNULL(col1), col1;Descending order, with NULLs first:
SELECT col1 FROM tab ORDER BY IF(col1 IS NULL, 0, 1), col1 DESC;All NULL values are also regarded as equivalent for the purposes of the DISTINCT and GROUP BY clauses.
订购
当您按可能包含 NULL 值的字段排序时，任何 NULL 都被视为具有最低值。因此，按 DESC 顺序排序将看到最后出现的 NULL。为了强制将 NULL 视为最高值，可以添加另一列，当主字段为 NULL 时，该列具有更高的值。例子：
SELECT col1 FROM tab ORDER BY ISNULL(col1), col1;降序，首先是 NULL：
SELECT col1 FROM tab ORDER BY IF(col1 IS NULL, 0, 1), col1 DESC;就 DISTINCT 和 GROUP BY 子句而言，所有 NULL 值也被视为等效。
The above shows two ways to order by NULL values, you can combine these with the ASC and DESC keywords as well. For example the other way to get the NULL values first would be:
上面显示了两种按 NULL 值排序的方法，您也可以将它们与 ASC 和 DESC 关键字结合使用。例如，首先获取 NULL 值的另一种方法是：
SELECT col1 FROM tab ORDER BY ISNULL(col1) DESC, col1;
-- ^^^^
