SQL Server Msg145报错：含DISTINCT的ORDER BY子句疑问

你可能会疑惑，明明field1在选择列表里，为什么还会报错？其实问题出在你的ORDER BY子句里的CASE表达式，而不是field1本身。

SQL Server在处理SELECT DISTINCT时，会先根据你指定的选择列（ID, field1, field2）去重，生成一个只包含这三列的中间结果集。而你用来排序的CASE WHEN field1 = 'myValue' THEN 1 ELSE 2 END是一个计算后的表达式，它并没有出现在这个去重后的结果集里。SQL Server要求，SELECT DISTINCT语句的ORDER BY项必须是选择列表中明确存在的列，或者是基于这些列的聚合函数（但这里的CASE不属于聚合），所以才会抛出这个错误。

有几种方式可以修改，既保留排序逻辑，又符合SQL Server的规则：

方法1：将CASE表达式加入选择列表（如果允许结果包含排序字段） #

直接把排序用的CASE表达式放到SELECT里，给它一个别名，然后ORDER BY这个别名即可：

SELECT DISTINCT 
    ID, 
    field1, 
    field2,
    CASE WHEN field1 = 'myValue' THEN 1 ELSE 2 END AS sort_order
FROM MyTable 
WHERE field1 = 'myValue' OR field2 = 'myValue' 
ORDER BY sort_order

这种方法最简单，但缺点是结果集会多出一个sort_order列。如果不需要这个列显示，可以用下面的方法。

方法2：使用CTE（公共表表达式）先查询所有数据，再去重排序 #

先通过CTE把需要的列和排序字段都查出来，然后在外层SELECT中去掉排序字段，同时利用CTE里的排序字段进行排序：

WITH SortedData AS (
    SELECT 
        ID, 
        field1, 
        field2,
        CASE WHEN field1 = 'myValue' THEN 1 ELSE 2 END AS sort_order
    FROM MyTable 
    WHERE field1 = 'myValue' OR field2 = 'myValue'
)
SELECT DISTINCT ID, field1, field2
FROM SortedData
ORDER BY sort_order

这种方法既保留了排序逻辑，又不会在最终结果里多出不必要的列，是比较推荐的做法。

方法3：使用子查询替代CTE #

如果你的SQL版本不支持CTE（不过现在主流版本都支持），也可以用子查询实现同样的效果：

SELECT DISTINCT ID, field1, field2
FROM (
    SELECT 
        ID, 
        field1, 
        field2,
        CASE WHEN field1 = 'myValue' THEN 1 ELSE 2 END AS sort_order
    FROM MyTable 
    WHERE field1 = 'myValue' OR field2 = 'myValue'
) AS TempTable
ORDER BY sort_order

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