Mysql，复杂 ORDER BY

Question

两列城镇和优先级。

我需要对表进行排序，以便优先级=1的城镇排在第一位，并且不按名称 ASC 排序，而其余城镇则按名称 ASC 排序。

我该怎么做？

谢谢;)

更新

SELECT *
FROM map_towns
ORDER BY priority DESC, town

像这样，但优先级是从 1 到 12+，而不是 12 到 1。

像这样:

town priority
b_town1 1
a_town2 2
d_town3 3
c_town4 4
a_town5 NULL
b_town6 NULL
c_town7 NULL
d_town8 NULL

等等...

Answer 1

By default, MySQL sorts nulls first

我创建了一个小测试用例(故意插入未排序的行)。

create table map_towns(
   town varchar(30) not null
  ,priority int null
);

insert into map_towns(town, priority) values('d_town3', 3);
insert into map_towns(town, priority) values('a_town2', 2);
insert into map_towns(town, priority) values('c_town4', 4);
insert into map_towns(town, priority) values('b_town1', 1);
insert into map_towns(town, priority) values('b_town6', NULL);
insert into map_towns(town, priority) values('d_town8', NULL);
insert into map_towns(town, priority) values('a_town5', NULL);
insert into map_towns(town, priority) values('c_town7', NULL);

以下查询应该满足您的要求。

select town
      ,priority
      ,isnull(priority) 
 from map_towns 
order by isnull(priority), priority, town;

+---------+----------+------------------+
| town    | priority | isnull(priority) |
+---------+----------+------------------+
| b_town1 |        1 |                0 |
| a_town2 |        2 |                0 |
| d_town3 |        3 |                0 |
| c_town4 |        4 |                0 |
| a_town5 |     NULL |                1 |
| b_town6 |     NULL |                1 |
| c_town7 |     NULL |                1 |
| d_town8 |     NULL |                1 |
+---------+----------+------------------+

这里是 ISNULL documentation 的链接