使用DISTINCT与CASE子句实现自定义排序时的问题及解决方法

错误原因 #

绝大多数关系型数据库（如MySQL、PostgreSQL、SQL Server）对DISTINCT和ORDER BY的组合有严格规则：ORDER BY子句中的表达式必须属于SELECT列表中的列/表达式，或是基于SELECT列表的聚合逻辑。

你之前用GROUP BY state_name能正常排序，是因为GROUP BY已经对state_name做了分组，ORDER BY的CASE表达式是基于分组后的state_name，属于分组逻辑内的合法引用。但添加DISTINCT后，若SELECT列表中没有包含ORDER BY里的CASE表达式，数据库无法将去重后的结果集与排序规则一一对应，因此触发语法逻辑错误。

单列DISTINCT场景修复方法 #

假设原错误查询为：

SELECT DISTINCT state_name 
FROM state_master 
ORDER BY CASE state_name 
           WHEN '北京' THEN 1 
           WHEN '上海' THEN 2 
           ELSE 3 
         END;

有两种可靠修复方式：

方式1：将CASE表达式纳入SELECT列表，通过别名排序 #

把排序用的CASE子句加到SELECT里，用别名指定排序依据，数据库就能关联去重后的结果与排序逻辑：

SELECT DISTINCT 
       state_name,
       CASE state_name 
         WHEN '北京' THEN 1 
         WHEN '上海' THEN 2 
         ELSE 3 
       END AS sort_order
FROM state_master 
ORDER BY sort_order;

如果不需要显示sort_order列，可以嵌套子查询或CTE过滤：

WITH sorted_states AS (
  SELECT DISTINCT 
         state_name,
         CASE state_name 
           WHEN '北京' THEN 1 
           WHEN '上海' THEN 2 
           ELSE 3 
         END AS sort_order
  FROM state_master
)
SELECT state_name 
FROM sorted_states 
ORDER BY sort_order;

方式2：先通过子查询去重，再外层排序 #

先在子查询中完成去重，外层查询直接基于去重后的结果使用CASE排序（此时外层SELECT的列与ORDER BY表达式直接关联，无需额外处理）：

SELECT state_name
FROM (
  SELECT DISTINCT state_name 
  FROM state_master
) AS distinct_states
ORDER BY CASE state_name 
           WHEN '北京' THEN 1 
           WHEN '上海' THEN 2 
           ELSE 3 
         END;

多列DISTINCT场景修复方法 #

假设需要对state_name和city_name两列去重，同时按state_name的自定义规则排序，原错误查询为：

SELECT DISTINCT state_name, city_name 
FROM state_master 
ORDER BY CASE state_name 
           WHEN '北京' THEN 1 
           WHEN '上海' THEN 2 
           ELSE 3 
         END;

同样适用两种修复逻辑：

方式1：纳入CASE表达式到SELECT列表 #

SELECT DISTINCT 
       state_name,
       city_name,
       CASE state_name 
         WHEN '北京' THEN 1 
         WHEN '上海' THEN 2 
         ELSE 3 
       END AS sort_order
FROM state_master 
ORDER BY sort_order;

如需隐藏排序列，用CTE或子查询：

WITH sorted_city_states AS (
  SELECT DISTINCT 
         state_name,
         city_name,
         CASE state_name 
           WHEN '北京' THEN 1 
           WHEN '上海' THEN 2 
           ELSE 3 
         END AS sort_order
  FROM state_master
)
SELECT state_name, city_name 
FROM sorted_city_states 
ORDER BY sort_order;

方式2：子查询先去重，外层排序 #

SELECT state_name, city_name
FROM (
  SELECT DISTINCT state_name, city_name 
  FROM state_master
) AS distinct_city_states
ORDER BY CASE state_name 
           WHEN '北京' THEN 1 
           WHEN '上海' THEN 2 
           ELSE 3 
         END;

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