由ASC订购，底部为空

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查询，将一个school表连接到一个district表。简单的一对多关系，每个学校附在一个地区。我的疑问如下:

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按地区名和学校名排序。唯一的问题是，我希望空区位于底部，这样我就可以在输出结束时使用一个名为“Other”的组。

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

是否可以在输出的末尾用null进行升序?

4 个解决方案

#1

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子句用例中，使null值比其他任何值都高:

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

#2

You could use the ISNULL() function.

可以使用ISNULL()函数。

From the MySQL manual:

从MySQL手册:

ISNULL(expr)

ISNULL(expr)

If expr is NULL, ISNULL() returns 1, otherwise it returns 0.

如果expr为NULL, ISNULL()返回1，否则返回0。

For example:

例如:

ORDER BY ISNULL(districts.name), districts.name, schools.name

I like to use this instead of the CASE option for MySQL. Just be aware that it's not portable since ISNULL() is not standard SQL and functions differently in other versions of SQL.

我喜欢用这个代替CASE选项。请注意，它是不可移植的，因为ISNULL()不是标准的SQL，在其他版本的SQL中函数也不一样。

#3

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...

缺省情况下，Nulls会出现在顶部，但您可以使用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

#4

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...)

在“new”colunm中加入更多的排序规则，并使用任何数字在代码中隐藏字段，测试它是否在if (order by if…)之后排序。

good luck

祝你好运

#1