如何在AdventureWorks2019中添加总计行？ROLLUP致数据重复

原查询在添加总计行时出现数据翻倍，是因为直接将ROLLUP/GROUP SETS与嵌套窗口函数结合，导致窗口函数对不同层级分组重复计算。以下是修正方案：

方法一：CTE+ROLLUP生成层级总计 #

先通过CTE计算每个年月的销售总和与年度累计，再基于CTE结果用ROLLUP生成年度总计和全局总计：

WITH MonthlySales AS (
    SELECT
        YEAR(s.OrderDate) AS Year,
        MONTH(s.OrderDate) AS Month,
        SUM(sd.UnitPrice) AS Sum_Price,
        SUM(SUM(sd.UnitPrice)) OVER (PARTITION BY YEAR(s.OrderDate) ORDER BY MONTH(s.OrderDate)) AS CumSum
    FROM
        Sales.SalesOrderDetail sd
    JOIN Sales.SalesOrderHeader s ON s.SalesOrderID = sd.SalesOrderID
    GROUP BY
        YEAR(s.OrderDate), MONTH(s.OrderDate)
)
SELECT
    CASE WHEN GROUPING(Year) = 1 THEN '总计' ELSE CAST(Year AS VARCHAR(4)) END AS Year,
    CASE WHEN GROUPING(Month) = 1 THEN '' ELSE CAST(Month AS VARCHAR(2)) END AS Month,
    SUM(Sum_Price) AS Sum_Price,
    CASE
        WHEN GROUPING(Year) = 1 THEN MAX(CumSum)
        WHEN GROUPING(Month) = 1 THEN MAX(CumSum)
        ELSE MAX(CumSum)
    END AS CumSum
FROM MonthlySales
GROUP BY ROLLUP(Year, Month)
ORDER BY 
    GROUPING(Year), Year, GROUPING(Month), Month;

方法二：CTE+UNION ALL拼接总计行 #

如果需要更灵活的总计展示，可分别查询年月数据、年度总计、全局总计后拼接：

WITH MonthlySales AS (
    SELECT
        YEAR(s.OrderDate) AS Year,
        MONTH(s.OrderDate) AS Month,
        SUM(sd.UnitPrice) AS Sum_Price,
        SUM(SUM(sd.UnitPrice)) OVER (PARTITION BY YEAR(s.OrderDate) ORDER BY MONTH(s.OrderDate)) AS CumSum
    FROM
        Sales.SalesOrderDetail sd
    JOIN Sales.SalesOrderHeader s ON s.SalesOrderID = sd.SalesOrderID
    GROUP BY
        YEAR(s.OrderDate), MONTH(s.OrderDate)
)
SELECT
    CAST(Year AS VARCHAR(4)) AS Year,
    CAST(Month AS VARCHAR(2)) AS Month,
    Sum_Price,
    CumSum
FROM MonthlySales
UNION ALL
-- 年度总计行
SELECT
    CAST(Year AS VARCHAR(4)) AS Year,
    '总计' AS Month,
    SUM(Sum_Price) AS Sum_Price,
    MAX(CumSum) AS CumSum
FROM MonthlySales
GROUP BY Year
UNION ALL
-- 全局总计行
SELECT
    '总计' AS Year,
    '' AS Month,
    SUM(Sum_Price) AS Sum_Price,
    MAX(CumSum) AS CumSum
FROM MonthlySales
ORDER BY
    CASE WHEN Year = '总计' THEN 2 ELSE 1 END,
    Year,
    CASE WHEN Month = '总计' THEN 13 ELSE CAST(Month AS INT) END;

问题说明 #

原查询直接使用ROLLUP时，SUM(SUM(sd.UnitPrice)) OVER (...)窗口函数会对ROLLUP生成的每个分组（年月、年、全局）重复计算累加，导致Sum_Price和CumSum数据翻倍。先通过CTE生成基础的年月统计结果，再基于该结果生成总计，能避免重复计算的问题。

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