MySQL 按 ASC 排序，底部为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5993109/
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
ORDER BY ASC with Nulls at the Bottom
提问by Dave Long
I'm writing an SQL query that connects a schools table to a districts table. Simple One-To-Many relationship where each school is attached to one district. My query is as follows:
我正在编写一个将学校表连接到地区表的 SQL 查询。简单的一对多关系，每所学校都隶属于一个地区。我的查询如下：
SELECT
schools.id AS schoolid,
schools.name AS school,
districts.id AS districtid,
districts.name AS district
FROM sms_schools AS schools
LEFT JOIN sms_districts AS districts ON schools.districtid = districts.id
WHERE 1 = 1
ORDER BY districts.name, schools.name
The reason I did a left join is because not every school is attached to a district. For example one school may be home schooled that may contain all students that are home schooled. That wouldn't be in a district.
我做左连接的原因是因为不是每所学校都附属于一个地区。例如，一所学校可能是在家上学的，其中可能包含所有在家上学的学生。那不会在一个区。
So what I would like to do is use the ORDER BY to order as it is by district name and then school name. The only problem is that I want the null district to be at the bottom so that I can then use a group called 'Other' at the end of my output.
所以我想做的是使用 ORDER BY 来排序，因为它是按地区名称然后是学校名称。唯一的问题是我希望空区位于底部，以便我可以在输出的末尾使用一个名为“其他”的组。
Is it possible to order by ascending with nulls at the end of the output?
是否可以在输出末尾用空值升序排序？
采纳答案by Dave Long
Only 1 minute after asking the question I found my answer. In the order by clause use case to make nulls have a higher value than anything else:
在提出问题后仅 1 分钟，我就找到了答案。在 order by 子句用例中，使空值比其他任何值都具有更高的值：
ORDER BY (CASE WHEN districts.id IS NULL then 1 ELSE 0 END),districts.name, schools.name;
回答by toxalot
You could use the ISNULL()function.
您可以使用该ISNULL()功能。
From the MySQL manual:
从MySQL 手册：
ISNULL(
expr)If
exprisNULL,ISNULL()returns1, otherwise it returns0.
为空(
expr)如果
expr是NULL，则ISNULL()返回1，否则返回0。
For example:
例如：
ORDER BY ISNULL(districts.name), districts.name, schools.name
I like to use this instead of the CASEoption for MySQL. Just be aware that it's not portablesince ISNULL()is not standard SQL and functions differently in other versions of SQL.
我喜欢使用它而不是CASEMySQL的选项。请注意，它不可移植，因为ISNULL()它不是标准 SQL，并且在其他版本的 SQL 中功能不同。
回答by M.R.
Nulls by default occur at the top, but you can use IsNull to assign default values, that will put it in the position you require...
默认情况下，空值出现在顶部，但您可以使用 IsNull 来分配默认值，这将把它放在您需要的位置......
SELECT schools.id AS schoolid,schools.name AS school, districts.id AS districtid, districts.name AS district FROM sms_schools AS schools LEFT JOIN sms_districts AS districts ON schools.districtid = districts.id WHERE 1 = 1
ORDER BY isnull(districts.name,'1'), schools.name
回答by borrel
SELECT
schools.id AS schoolid,
schools.name AS school,
districts.id AS districtid,
districts.name AS district,
if(schools.districtid IS NULL,1,0) as sort
FROM sms_schools AS schools
LEFT JOIN sms_districts AS districts
ON schools.districtid = districts.id
WHERE 1 = 1
ORDER BY sort, districts.name, schools.name
put any more sort rules insite the 'new' colunm and use any number hide the field in your code, test if it is possebele to sort on the if dirctly(order by if...)
在“新”列中放置更多排序规则并使用任何数字隐藏代码中的字段，测试是否可以直接对 if 进行排序（按 if...排序）
good luck
祝你好运
