单列查询下使用SELECT DISTINCT+ORDER BY+CASE WHEN遇报错的原因解析及解决方案

为什么会出现这个错误？ #

你肯定会纳闷：我明明只查了LanguageCode这一列，排序用的CASE表达式也是基于它的，为啥会弹出ORDER BY items must appear in the select list if SELECT DISTINCT is specified这个错误？

其实这是SQL的一个硬性规则：当使用SELECT DISTINCT时，ORDER BY子句里的所有排序依据（不管是字段还是计算表达式）都必须包含在SELECT的结果列表中。

原因在于SELECT DISTINCT的执行逻辑：它会先对查询结果进行去重处理，这个过程会打乱原有的数据顺序，SQL引擎需要明确知道，用来排序的依据是去重后结果集里存在的内容。你的CASE表达式是一个临时计算出来的值，并没有出现在SELECT的输出列里，所以引擎无法用它来给去重后的结果排序。

解决方法 #

这里给你两种简单直接的方案：

方案1：用子查询包裹，把排序表达式放到内层查询中

先在内层查询里同时返回LanguageCode和排序用的CASE表达式，去重后再在外层只取LanguageCode并排序：

SELECT LanguageCode
FROM (
    SELECT DISTINCT 
        LanguageCode,
        CASE WHEN LanguageCode='GB' THEN 0 ELSE 1 END AS SortOrder
    FROM myTable
) AS temp
ORDER BY SortOrder, LanguageCode

方案2：用GROUP BY替代DISTINCT实现去重

GROUP BY的执行逻辑和DISTINCT不同，它是先分组去重，再处理排序，这时ORDER BY里的表达式不需要出现在SELECT列表中：

SELECT LanguageCode
FROM myTable
GROUP BY LanguageCode
ORDER BY (CASE WHEN LanguageCode='GB' THEN 0 ELSE 1 END), LanguageCode

这两种方法都能实现你的需求：获取所有不重复的语言代码，并且让GB排在最前面。

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