如何修复含条件CASE的SQL Server GROUP BY查询聚合问题

报错的核心原因是SELECT子句中嵌套CASE表达式的逻辑和GROUP BY子句中的CASE逻辑不匹配。GROUP BY里仅对customer_score做了简单的条件返回，而SELECT里在这个基础上又嵌套了一层判断逻辑，SQL Server无法识别该嵌套表达式属于GROUP BY的分组项，因此抛出Column '@orders_table.customer_score' is invalid错误。

以下是两种无需动态SQL的解决方法：

方法一：将SELECT中的嵌套CASE完全同步到GROUP BY #

直接把SELECT里的嵌套CASE表达式复制到GROUP BY中，保证非聚合列的表达式和分组依据完全一致：

DECLARE @orders_table TABLE(
i_date DATE,
customer_id INT,
customer_score INT,
orders INT
);

INSERT @orders_table
VALUES
('2022-11-28', 100, 3, 1),
('2022-11-28', 101, 7, 4),
('2022-11-29', 102, 6, 1),
('2022-11-30', 103, 1, 5)

DECLARE @group_by_customer_id BIT = 1;
SELECT
i_date,
CASE WHEN @group_by_customer_id = 1 THEN customer_id ELSE NULL END AS customer_id_grouped,
CASE WHEN @group_by_customer_id = 1 THEN CASE WHEN customer_score > 5 THEN customer_score ELSE 0 END ELSE NULL END AS customer_score_calculated,
SUM(orders) AS total_orders
FROM
@orders_table
GROUP BY
i_date,
CASE WHEN @group_by_customer_id = 1 THEN customer_id ELSE NULL END,
CASE WHEN @group_by_customer_id = 1 THEN CASE WHEN customer_score > 5 THEN customer_score ELSE 0 END ELSE NULL END;

方法二：用CTE预先计算嵌套逻辑，再分组 #

通过公共表表达式（CTE）提前处理好所有需要的分组列和计算列，再对CTE结果进行分组，逻辑更清晰且避免重复写CASE：

DECLARE @orders_table TABLE(
i_date DATE,
customer_id INT,
customer_score INT,
orders INT
);

INSERT @orders_table
VALUES
('2022-11-28', 100, 3, 1),
('2022-11-28', 101, 7, 4),
('2022-11-29', 102, 6, 1),
('2022-11-30', 103, 1, 5)

DECLARE @group_by_customer_id BIT = 1;

WITH pre_calculated AS (
    SELECT
        i_date,
        CASE WHEN @group_by_customer_id = 1 THEN customer_id ELSE NULL END AS customer_id_grouped,
        CASE WHEN @group_by_customer_id = 1 THEN CASE WHEN customer_score > 5 THEN customer_score ELSE 0 END ELSE NULL END AS customer_score_calculated,
        orders
    FROM @orders_table
)
SELECT
    i_date,
    customer_id_grouped,
    customer_score_calculated,
    SUM(orders) AS total_orders
FROM pre_calculated
GROUP BY
    i_date,
    customer_id_grouped,
    customer_score_calculated;

两种方法都能满足按@group_by_customer_id参数动态分组的需求，且完全规避动态SQL的使用。

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