SQL Server 2014中如何用ROLLUP实现小计及行占总计百分比？

嘿，这个需求我之前处理销售汇总报表的时候刚好碰到过，SQL Server 2014里结合ROLLUP生成层级小计，同时计算每行占全局总计的百分比其实很清晰，关键是用窗口函数获取全局总销售额，下面给你完整的实现方案：

完整SQL语句 #

WITH RollupData AS (
    SELECT 
        Week,
        Category,
        Collection,
        SUM(Sales) AS TotalSales
    FROM myTable
    GROUP BY Week, Category, Collection WITH ROLLUP
)
SELECT 
    -- 替换ROLLUP生成的NULL为更易读的标签
    COALESCE(CAST(Week AS VARCHAR(10)), '所有周') AS Week,
    COALESCE(Category, '全分类') AS Category,
    COALESCE(Collection, '全收集类型') AS Collection,
    TotalSales,
    -- 计算占全局总计的百分比，保留两位小数
    CAST(100.0 * TotalSales / SUM(TotalSales) OVER () AS DECIMAL(5,2)) AS PercentageOfTotal
FROM RollupData
ORDER BY 
    -- 确保ROLLUP的层级顺序正确（明细→分类小计→周小计→总计）
    CASE WHEN Week IS NULL THEN 2 ELSE 1 END,
    Week,
    CASE WHEN Category IS NULL THEN 2 ELSE 1 END,
    Category,
    CASE WHEN Collection IS NULL THEN 2 ELSE 1 END,
    Collection;

关键部分解释 #

1. ROLLUP生成层级小计：
   你的原始GROUP BY WITH ROLLUP已经能生成四个层级的汇总：

   • 最细粒度：按Week+Category+Collection分组的明细行
   • 第一级小计：按Week+Category分组（Collection为NULL）
   • 第二级小计：按Week分组（Category和Collection为NULL）
   • 全局总计：所有行汇总（Week、Category、Collection全为NULL）

2. 获取全局总计：
   用窗口函数SUM(TotalSales) OVER ()直接计算所有汇总行的总销售额，这个函数会忽略分组，直接对整个CTE的TotalSales列求和，拿到全局的总销售额（你的示例数据里总销售额是22）。

3. 避免整数除法：
   计算百分比的时候用100.0而不是100，这样SQL Server会把运算转换成浮点型，避免整数除法导致的精度丢失（比如4/22如果用整数除法会得到0，乘以100还是0）。

4. 优化可读性：
   用COALESCE把ROLLUP生成的NULL值替换成“所有周”、“全分类”这样的文本，让报表更易读；同时通过ORDER BY子句确保汇总行的顺序符合逻辑，不会出现小计行穿插在明细行里的情况。

示例数据运行结果 #

针对你给出的示例数据，运行后会得到类似这样的结果（截取部分）：

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