按ASC排序，底部为空 [英] ORDER BY ASC with Nulls at the Bottom

问题描述

我正在编写一个SQL查询，该查询将Schools表连接到districts表.每个学校都隶属于一个地区的简单一对多关系.我的查询如下:

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:

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.

所以我想做的是使用ORDER BY来按地区名称和学校名称进行排序.唯一的问题是，我希望空区域位于底部，以便可以在输出末尾使用一个名为其他"的组.

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.

是否可以通过在输出末尾以null升序进行排序?

Is it possible to order by ascending with nulls at the end of the output?

推荐答案

问了问题仅1分钟后，我就找到了答案.在order by子句用例中，使null具有比其他任何东西更高的值:

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:

 ORDER BY (CASE WHEN districts.id IS NULL then 1 ELSE 0 END),districts.name, schools.name;

这篇关于按ASC排序，底部为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！