SQL Server分组二次聚合、嵌套聚合报错及动态聚合实现问题

SQL Server 原生不支持直接嵌套聚合函数（如AVG(SUM(value))的写法），你遇到的报错正是因为直接在内层聚合结果上执行外层聚合导致的。你需要先通过子查询或CTE将内层聚合的计算结果落地，再在外层执行第二次聚合。

DECLARE @target_year INT = 2024 -- 替换为指定年份
DECLARE @target_course_id INT = 1 -- 替换为指定课程ID

-- 用CTE先计算每个时段的总分
WITH period_total AS (
    SELECT 
        tp.period_index,
        SUM(tp.value) AS period_sum
    FROM test_results tr
    INNER JOIN test_periods tp ON tr.id = tp.result_id
    WHERE tr.year = @target_year
      AND tr.course_id = @target_course_id
    GROUP BY tp.period_index
)
-- 外层计算所有时段总分的平均值
SELECT AVG(period_sum) AS final_result
FROM period_total

以上查询基于你的示例数据会返回15.5的结果。

由于聚合函数名无法直接通过变量传入，需要通过动态SQL实现，同时增加聚合函数合法性校验避免SQL注入风险：

DECLARE @target_year INT = 2024
DECLARE @target_course_id INT = 1
DECLARE @inner_agg_method NVARCHAR(10) = 'SUM' -- 可替换为AVG/MAX/MIN等
DECLARE @outer_agg_method NVARCHAR(10) = 'AVG' -- 可替换为AVG/MAX/MIN等
DECLARE @sql NVARCHAR(MAX)

-- 先校验聚合函数是否为允许的取值，避免注入
IF @inner_agg_method NOT IN ('SUM','AVG','MAX','MIN') 
    OR @outer_agg_method NOT IN ('SUM','AVG','MAX','MIN')
BEGIN
    RAISERROR('不支持的聚合方法，仅允许SUM/AVG/MAX/MIN',16,1)
    RETURN
END

SET @sql = N'
WITH period_agg AS (
    SELECT 
        tp.period_index,
        ' + @inner_agg_method + '(tp.value) AS inner_agg_val
    FROM test_results tr
    INNER JOIN test_periods tp ON tr.id = tp.result_id
    WHERE tr.year = @in_year
      AND tr.course_id = @in_course_id
    GROUP BY tp.period_index
)
SELECT ' + @outer_agg_method + '(inner_agg_val) AS final_result
FROM period_agg
'

-- 执行动态SQL，参数化传值避免注入
EXEC sp_executesql @sql,
    N'@in_year INT, @in_course_id INT',
    @in_year = @target_year,
    @in_course_id = @target_course_id

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