mysql - 如何执行多级 ORDER BY，其中具有特定值的行将获得优先权？

Question

示例表

+------+--------+------------------+
| Code |  City  |       Role       |
+------+--------+------------------+
|    1 | Mumbai | Designer         |
|    2 | Mumbai | Designer         |
|    3 | Delhi  | Designer         |
|    4 | Delhi  | Project Manager  |
|    5 | Mumbai | Project Manager  |
|    6 | Pune   | Designer         |
|    7 | London | Network Engineer |
+------+--------+------------------+

第 1 级排序将在城市字段上进行 - 首先是德里，然后是孟买，然后是其他任何地方。第 2 级排序将在角色字段上进行 - 首先是项目经理，然后是设计师，然后是其他任何人。

排序后的输出应该是

+------+--------+------------------+
| Code |  City  |       Role       |
+------+--------+------------------+
|    4 | Delhi  | Project Manager  |
|    3 | Delhi  | Designer         |
|    5 | Mumbai | Project Manager  |
|    1 | Mumbai | Designer         |
|    2 | Mumbai | Designer         |
|    6 | Pune   | Designer         |
|    7 | London | Network Engineer |
+------+--------+------------------+

Answer 1

在 MySQL 中，一种方法是:

order by (case when city = 'Delhi' then 1
               when city = 'Mumbai' then 2
               else 3
          end),
         city,
         (case when role = 'Project Manager' then 1
               when role = 'Designer' then 2
               else 3
          end)

一个更短但有点神秘的方法使用:

order by field(city, 'Mumbai', 'Delhi') desc,
         city,
         field(role, 'Designer', 'Project Manager') desc

field() 在没有匹配项时返回 0。因此，参数的顺序与您希望它们的搜索方式相反。 desc 然后将它们按正确的顺序排列。