MSSQL Server中GROUP BY使用别名报错的原因及解决方法

错误原因 #

SQL Server的查询执行顺序中，GROUP BY子句会先于SELECT子句执行，这意味着当执行分组操作时，SELECT里定义的person1和person2别名还未被创建，因此会抛出“Invalid column name 'person1'”的错误。

修复方法 #

有两种常见的修复方式：

方法1：在GROUP BY中重复CASE表达式

直接将SELECT里用于生成person1、person2的CASE逻辑复制到GROUP BY中：

SELECT 
    CASE 
        WHEN from_id > to_id THEN to_id
        ELSE from_id
    END AS person1,
    CASE 
        WHEN from_id > to_id THEN from_id
        ELSE to_id
    END AS person2,
    COUNT(duration) AS call_count,
    SUM(duration) AS total_duration
FROM Calls
GROUP BY 
    CASE 
        WHEN from_id > to_id THEN to_id
        ELSE from_id
    END,
    CASE 
        WHEN from_id > to_id THEN from_id
        ELSE to_id
    END

方法2：使用CTE（公共表表达式）预先计算列

先通过CTE将person1、person2计算出来，再对CTE的结果进行分组：

WITH CallPairs AS (
    SELECT 
        CASE 
            WHEN from_id > to_id THEN to_id
            ELSE from_id
        END AS person1,
        CASE 
            WHEN from_id > to_id THEN from_id
            ELSE to_id
        END AS person2,
        duration
    FROM Calls
)
SELECT 
    person1,
    person2,
    COUNT(duration) AS call_count,
    SUM(duration) AS total_duration
FROM CallPairs
GROUP BY person1, person2

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