MySQL：如何按列升序排序，并在末尾而不是开头显示NULL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3514298/
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
MySQL: How to sort by column in ascending order, and show NULL at the end instead of the beginning?
提问by Andrew
I know how to sort a result set:
我知道如何对结果集进行排序：
SELECT * FROM `People` ORDER BY `LastName` ASC
However, the results that have an empty LastName show at the beginning. How do I sort in ascending order, and show the NULL results at the end instead of the beginning?
但是，具有空姓氏的结果显示在开头。如何按升序排序，并在末尾而不是开头显示 NULL 结果？
采纳答案by Tom H
SELECT
*
FROM
People
ORDER BY
CASE WHEN LastName IS NULL THEN 1 ELSE 0 END,
LastName
You could also simply use
你也可以简单地使用
SELECT
*
FROM
People
ORDER BY
COALESCE(LastName, 'ZZZZZ')
Technically, the second version would fail if a person actually had a LastName in your DB of "ZZZZZZ".
从技术上讲，如果一个人在您的数据库中确实有“ZZZZZZ”的姓氏，那么第二个版本就会失败。
NOTE: I'm sure it's just because you're giving an example, but I hope you're not using SELECT * in actual production code... :)
注意：我确定这只是因为您在举一个例子，但我希望您没有在实际生产代码中使用 SELECT * ... :)
回答by NullUserException
SELECT *, LastName IS NULL AS nullity
FROM `People`
ORDER BY nullity ASC, `LastName` ASC
回答by ovais.tariq
this should do it for you
这应该为你做
select *, if(isnull(name), 1, 0) as is_name_null
from names
order by is_name_null asc, name asc
回答by RedFilter
SELECT *
FROM `People`
ORDER BY case when `LastName` is null then 1 else 0 end,
`LastName`
