如何编写逻辑合理且结果正确的ORDER BY CASE排序SQL语句？

先看我们的folders表结构：

+----+--------+------+
| id | folder | user |
+----+--------+------+
| 1  | Inbox  | 0    |
+----+--------+------+
| 2  | Drafts | 0    |
+----+--------+------+
| 3  | Sent   | 0    |
+----+--------+------+
| 4  | Spam   | 0    |
+----+--------+------+
| 5  | Second | 1    |
+----+--------+------+
| 6  | First  | 1    |
+----+--------+------+

需求明确：查询时先输出user='0'的系统文件夹（按id升序），再输出user='1'的用户文件夹（按folder名称升序），期望结果顺序为：

1. Inbox
2. Drafts
3. Sent
4. Spam
5. First
6. Second

之前的颠倒CASE语句虽能得到正确结果，但逻辑混乱：

SELECT id, folder, user 
FROM folders 
ORDER BY 
(CASE WHEN user='1' THEN folder END) ASC,
(CASE WHEN user='0' THEN user END) ASC;

而按正常逻辑顺序编写的CASE语句却返回错误结果，把用户文件夹排在了前面：

SELECT id, folder, user 
FROM folders 
ORDER BY 
(CASE WHEN user='0' THEN user END) ASC,
(CASE WHEN user='1' THEN folder END) ASC;

正确且逻辑清晰的查询写法 #

要实现需求，需先区分两类文件夹的优先级，再分别指定各自的排序规则：

SELECT id, folder, user 
FROM folders 
ORDER BY 
  -- 第一优先级：系统文件夹(user='0')排在前，用户文件夹(user='1')在后
  CASE WHEN user = '0' THEN 0 ELSE 1 END ASC,
  -- 第二优先级：系统文件夹按id升序排序
  CASE WHEN user = '0' THEN id ELSE NULL END ASC,
  -- 第三优先级：用户文件夹按folder名称升序排序
  CASE WHEN user = '1' THEN folder ELSE NULL END ASC;

为什么之前的颠倒写法能工作？ #

之前的颠倒CASE语句生效是因为：user='1'时返回folder值，user='0'时返回user值（即0）。排序时，系统文件夹的第一个CASE值为NULL，用户文件夹的第一个CASE值是具体名称；而数据库对NULL的排序行为刚好让系统文件夹的第二个CASE值（0）排在用户文件夹的第二个CASE值（NULL）前面。这种写法依赖数据库的NULL排序规则，逻辑模糊，稳定性差。

逻辑清晰写法的优势 #

正确写法明确分为三个排序层级：

1. 用固定值0和1强制区分两类文件夹的优先级，不受数据库NULL排序规则影响，确保系统文件夹始终在前。
2. 仅针对系统文件夹按id排序，用户文件夹此处返回NULL，不干扰排序。
3. 仅针对用户文件夹按folder名称排序，系统文件夹此处返回NULL，不干扰排序。

该写法逻辑一目了然，能稳定输出预期结果。

内容的提问来源于stack exchange，提问作者John