SQL如何使空值在升序排序时排在最后？

Question

我有一个带有日期时间字段的SQL表格。该字段可以为空。我有一个查询，我想按照日期时间字段进行升序排序，但我希望日期时间字段为空的行出现在列表的最后而不是最前面。

是否有简单的方法来实现这个需求？

- David Božjak

---

可以使用 SQL 按日期排序，但将空日期放在结果的末尾吗？ - Bill Karwin

请参见https://dev59.com/r2Up5IYBdhLWcg3wrY9p#35494930和https://code.djangoproject.com/ticket/13312。 - Risadinha

Answer 1

select MyDate
from MyTable
order by case when MyDate is null then 1 else 0 end, MyDate

- D'Arcy Rittich

---

请注意，如果您在排序列上放置索引以提高性能(*)，则这种方法会使查询计划变得复杂，并且失去大部分性能优势。 *-索引提供了预先排序的数据，因此避免了每次查询执行的排序。如果可能的话，建议过滤掉NULL记录，以完全避免出现此问题。 - redcalx

这里还提供了一个很好的答案，列举了所有可能的方法以及优缺点。http://nickstips.wordpress.com/2010/09/30/sql-order-by-with-null-values-last/ - sudhAnsu63

“order by case when MyDate is null then 1 else 0 end”这句话的意思是“按照MyDate是否为空进行排序”，但这种写法比较冗长。简洁的表达方式是“ORDER BY MyDate IS NULL”。请注意，这两种表达方式意思相同，只是语言表述不同。 - Martin

请注意，这个问题没有标记为MySQL。我提供了一个通用的解决方案 - 在不同的数据库中，有很多不同的方式来完成相同的事情。 - D'Arcy Rittich

@KyleDelaney 因为 order by 0 被解释为列索引，而列索引是从1开始的。要按第三列排序查询，可以使用 order by 3（这对于生产查询来说是一个可怕的想法），但在实验时非常方便（就像使用 * 一样）。 - D'Arcy Rittich

Answer 2

（有点晚了，但这个问题还没有被提到过）

您没有指定DBMS。

在标准的SQL（以及大多数现代的DBMS如Oracle、PostgreSQL、DB2、Firebird、Apache Derby、HSQLDB和H2）中，您可以指定NULLS LAST或NULLS FIRST：

使用NULLS LAST将它们排序到末尾：

select *
from some_table
order by some_column DESC NULLS LAST

- user330315

---

据我所知，SQL:2003添加了NULLS FIRST和NULLS LAST，但在不同的数据库管理系统中并没有标准实现。根据数据库引擎的不同，可以使用以下命令进行排序：Oracle使用ORDER BY expr some_column DESC NULLS LAST，MSSQL使用ORDER BY ISNULL(some_column, 1), some_column ASC，而MySQL则使用不同的ISNULL()实现，如ORDER BY ISNULL(some_column), some_column ASC。请注意，这些命令只是针对特定的数据库引擎有效。 - SaschaM78

默认的 NULL 排序方式取决于数据库管理系统。有些将它们排序到最后，有些则排序到最前。有些在处理 null 时不关心 ASC/DESC，而有些则会考虑。因此，确保排序的唯一方法是使用“NULL FIRST/LAST”，如果数据库支持的话。这样可以记录下你的意图。使用“isnull()”或其他函数是解决缺少“NULLS FIRST/LAST”支持的一种方法（Oracle、PostgreSQL、DB2、Firebird、Apache Derby、HSQLDB 和 H2 支持）。 - user330315

看起来很有前途，但遗憾的是我在MySQL数据库中尝试了NULLS LAST，但它没有起作用。 - RedDragonWebDesign

@AdmiralAdama：你可以随时升级到Postgres。 - user330315

还有，为什么我们要将 null 映射到 1？ - MasterJoe

Answer 3

我也刚刚偶然发现了这个，并且以下内容对我在MySQL和PostgreSQL上似乎很有效：

ORDER BY date IS NULL, date DESC

如发现在https://dev59.com/42025IYBdhLWcg3wvIm2#7055259

- Luksurious

---

这看起来很有前途，但遗憾的是，我尝试了一下，在我的MySQL数据库中IS NULL和IS NOT NULL没有起作用。 - RedDragonWebDesign

Answer 4

如果您的引擎允许使用 ORDER BY x IS NULL, x 或者 ORDER BY x NULLS LAST，请使用这些语句。但如果不支持这些语句，可以尝试以下方法：

如果您正在按数值类型排序，可以这样做：（从另一篇回答中借用模式。）

SELECT *          
FROM Employees
ORDER BY ISNULL(DepartmentId*0,1), DepartmentId;

任何非空数字变为 0，而 null 变为 1，这使得 null 值最后排序，因为 0 < 1。

您也可以对字符串执行此操作：

SELECT *
FROM Employees
ORDER BY ISNULL(LEFT(LastName,0),'a'), LastName

任何非空字符串变成 ''，而空值变成 'a'，这样做是为了使空值排在最后，因为 '' < 'a'。

这个方法甚至适用于日期，通过将其强制转换为可为空的整数并使用上面的整数方法来实现：

SELECT *
FROM Employees
ORDER BY ISNULL(CONVERT(INT, HireDate)*0, 1), HireDate

（假装模式拥有HireDate架构。）

这些方法避免了必须为每种类型想出或管理“最大”值，或者在数据类型（和最大值）更改时修复查询的问题（其他ISNULL解决方案都存在这些问题）。此外，它们比CASE短得多。

- infogulch

Answer 5

您可以使用内置函数检查是否为null或非null，如下所示。我测试过了，它的工作正常。

选择 MyDate 从 MyTable 按 ISNULL(MyDate,1) DESC，MyDate ASC的顺序排列；

- Majdi M. Aburahelah

---

为了让日期与mssql配合使用，我发现在ISNULL函数中放置一个遥远的未来日期很有用，例如：ISNULL(MyDate,'2100-01-01')。 - Emi-C

在MySQL中，它说我传递了太多参数。我通过执行ISNULL(MyDate) DESC, MyDate ASC来解析它，但它没有按正确的顺序排序。 - RedDragonWebDesign

Answer 6

order by coalesce(date-time-field,large date in future)

- Gratzy

---

尽管这个方法通常可行，但应该注意到它存在一些问题：将来的大日期可能会与实际数据冲突或小于实际数据，导致排序不完美。此外，这是一种“魔数”解决方案，无法自我说明。 - D'Arcy Rittich

当您需要将问题列与另一个日期列进行比较时，此方法是@RedFilter答案的绝佳替代方案。我用它来联合资历表。如果员工有资格日期（可为空），那么该记录会排在前面，否则使用HireDate。使用ORDER BY ISNULL(QualifiedDate,'1-1-2099')，HireDate，LastName等可以使Qualified Date不与HiredDate日期产生冲突，并生成正确的资历列表。 - Alan Fisher

Answer 7

如果您的排序列是数字（如排名），则可以将其乘以-1，然后按降序排序。这样可以保持您期望的顺序，但将NULL放在最后。

select *
from table
order by -rank desc

- Luizgrs

---

刚想评论这个。从 https://dev59.com/AXI-5IYBdhLWcg3wBjhR#8174026 学到了这个技巧，非常好用。 - Chris

Answer 8

在Oracle中，您可以使用NULLS FIRST或NULLS LAST：指定应该在非空值之前/之后返回NULL值：

ORDER BY { column-Name | [ ASC | DESC ] | [ NULLS FIRST | NULLS LAST ] }

例如：

ORDER BY date DESC NULLS LAST

参考：http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj13658.html

- Joaquinglezsantos

Answer 9

如果您正在使用MariaDB，他们在“NULL Values documentation”中提到以下内容。

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值排序的方法，你也可以将它们与ASC和DESC关键字结合使用。例如，获取NULL值排在前面的另一种方法是：

SELECT col1 FROM tab ORDER BY ISNULL(col1) DESC, col1;
--                                         ^^^^

- 3limin4t0r

Answer 10

SELECT *          
FROM Employees
ORDER BY ISNULL(DepartmentId, 99999);

请参考这篇博客文章。

- user3923117