SQL 按列 ASC 排序，但首先是 NULL 值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9510509/
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
Sort by column ASC, but NULL values first?
提问by mhd
I need to sort a PostgreSQL table ascending by a date/time field, e.g. last_updated.
我需要按日期/时间字段升序对 PostgreSQL 表进行排序，例如last_updated.
But that field is allowed to be empty or null and I want records with null in last_updatedcome beforenon-null last_updated.
Is this possible?
但是，该字段允许为空或空，我想与空记录last_updated来之前，非空last_updated。
这可能吗？
order by last_updated asc -- and null last_updated records first ??
回答by Erwin Brandstetter
Postgres provides the NULLS FIRST | LASTkeywords for the ORDER BYclause to cater for that need exactly:
PostgresNULLS FIRST | LAST为该ORDER BY子句提供了关键字以完全满足该需求：
... ORDER BY last_updated NULLS FIRST
A typicaluse case is with descending sort order (DESC), which yields the complete inversion of the default ascending order (ASC) with null values first. Often not desirable - so, to keep null values last:
一个典型的用例是使用降序排序 ( DESC)，它ASC首先使用空值生成默认升序 ( )的完全反转。通常不可取 - 所以，最后保持空值：
... ORDER BY last_updated DESC NULLS LAST
To support the query with an index, make it match:
要支持带有索引的查询，请使其匹配：
CREATE INDEX foo_idx ON tbl (last_updated DESC NULLS LAST);
Postgres can read btree indexes backwards, but it matters where NULL values are appended.
Postgres 可以向后读取 btree 索引，但重要的是追加 NULL 值的位置。
回答by mechanical_meat
You can create a custom ORDER BY using a CASE statement.
The CASE statement checks for your condition and assigns to rows which meet that condition a lower value than that which is assigned to rows which do not meet the condition.
It's probably easiest to understand given an example:
您可以使用 CASE 语句创建自定义 ORDER BY。
CASE 语句检查您的条件并为满足该条件的行分配一个比分配给不满足该条件的行的值低的值。
举个例子可能最容易理解：
SELECT last_updated
FROM your_table
ORDER BY CASE WHEN last_updated IS NULL THEN 0 ELSE 1 END,
last_updated ASC;
