如何用参数结合CASE表达式动态指定SQL的GROUP BY子句？

尝试用变量结合CASE表达式动态指定GROUP BY子句时，遇到错误：

Msg 8120, Level 16, State 1, Line 77
Column 'Field_1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

使用的示例SQL：

DECLARE @OrderBy nvarchar(15) = 'Date';
DECLARE @StartDate date = '01/01/2023';
DECLARE @EndDate date = '12/31/2023';

SELECT Field_1, Field_2, Field_3, SUM(Field_4) AS 'Sum'
FROM Table_1
WHERE
    Field_2 IN ('A', 'B', 'C', 'D', 'E')
    AND Field_3 > 1
    AND Field_3 < 20
    AND Field_1 >= @StartDate
    AND Field_1 <= @EndDate

/*
    If @OrderBy = 'Date' 
        THEN "GROUP BY" should be
            GROUP BY Field_1, Field_2, Field_3
    IF @OrderBy = 'Location'
        THEN "GROUP BY" should be
            GROUP BY Field_3, Field_2, Field_1
*/
GROUP BY
    CASE WHEN @OrderBy = 'Date' 
        THEN [Field_1]
    END, 
    CASE WHEN @OrderBy = 'Date' 
        THEN [Field_2]
    END,
    CASE WHEN @OrderBy = 'Date' 
        THEN [Field_3]
    END,
    CASE WHEN @OrderBy = 'Location' 
        THEN [Field_2]
    END, 
    CASE WHEN @OrderBy = 'Location' 
        THEN [Field_3]
    END,
    CASE WHEN @OrderBy = 'Location' 
        THEN [Field_1]
    END

需求是根据@OrderBy参数值，让查询按Field_1, Field_2, Field_3或Field_3, Field_2, Field_1分组，用单个存储过程处理多分组场景，不创建多个存储过程。

可行解决方法 #

方法1：动态SQL（精准匹配需求） #

动态SQL可以直接根据参数拼接GROUP BY子句，是最直接的实现方式，同时通过参数化避免注入风险：

CREATE PROCEDURE DynamicGroupByProcedure
    @OrderBy nvarchar(15),
    @StartDate date,
    @EndDate date
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL nvarchar(max);
    DECLARE @GroupByClause nvarchar(100);

    -- 根据参数确定GROUP BY子句
    SET @GroupByClause = CASE @OrderBy
        WHEN 'Date' THEN 'Field_1, Field_2, Field_3'
        WHEN 'Location' THEN 'Field_3, Field_2, Field_1'
        ELSE 'Field_1, Field_2, Field_3' -- 默认分组规则
    END;

    -- 拼接完整SQL语句
    SET @SQL = N'
    SELECT Field_1, Field_2, Field_3, SUM(Field_4) AS ''Sum''
    FROM Table_1
    WHERE
        Field_2 IN (''A'', ''B'', ''C'', ''D'', ''E'')
        AND Field_3 > 1
        AND Field_3 < 20
        AND Field_1 >= @StartDate
        AND Field_1 <= @EndDate
    GROUP BY ' + @GroupByClause + N'
    ORDER BY ' + @GroupByClause; -- 可选：按分组列排序

    -- 执行动态SQL并传入参数
    EXEC sp_executesql @SQL, 
        N'@StartDate date, @EndDate date',
        @StartDate = @StartDate,
        @EndDate = @EndDate;
END;

方法2：利用分组特性简化实现（推荐） #

注意：GROUP BY Field_1, Field_2, Field_3和GROUP BY Field_3, Field_2, Field_1的分组结果完全一致——分组是基于列的唯一组合，和列的顺序无关。如果你的实际需求只是最终结果的排序不同，无需修改GROUP BY，只需要动态指定ORDER BY即可：

DECLARE @OrderBy nvarchar(15) = 'Date';
DECLARE @StartDate date = '01/01/2023';
DECLARE @EndDate date = '12/31/2023';

SELECT Field_1, Field_2, Field_3, SUM(Field_4) AS 'Sum'
FROM Table_1
WHERE
    Field_2 IN ('A', 'B', 'C', 'D', 'E')
    AND Field_3 > 1
    AND Field_3 < 20
    AND Field_1 >= @StartDate
    AND Field_1 <= @EndDate
GROUP BY Field_1, Field_2, Field_3
ORDER BY
    CASE WHEN @OrderBy = 'Date' THEN Field_1 END,
    CASE WHEN @OrderBy = 'Date' THEN Field_2 END,
    CASE WHEN @OrderBy = 'Date' THEN Field_3 END,
    CASE WHEN @OrderBy = 'Location' THEN Field_3 END,
    CASE WHEN @OrderBy = 'Location' THEN Field_2 END,
    CASE WHEN @OrderBy = 'Location' THEN Field_1 END;

这种写法无需动态SQL，更安全简洁，适合仅需调整排序的场景。

原写法错误原因说明 #

原代码报错是因为：SELECT列表中的列必须以直接列名出现在GROUP BY中，或被聚合函数包裹。用CASE表达式返回列值时，SQL Server会将CASE结果视为新表达式，而非原列，导致SELECT中的列不在GROUP BY的有效范围内，触发8120错误。

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