如何在SQL Server 2019中用GROUP BY ROLLUP计算小计与总计

问题场景 #

现有G_Dashboard表结构及数据如下：

CREATE TABLE [dbo].[G_Dashboard] 
(
    [orderId] int  NULL,
    [C_CO] varchar(4) NULL,
    [D_CO] varchar(50) NULL,
    [Total] int  NULL,
    [A_RG] int  NULL,
    [B_RG] int NULL,
    [C_RG] int  NULL,
    [D_RG] int  NULL,
    [E_RG] int  NULL,
    [DateHour] datetime  NULL,
)

INSERT INTO [dbo].[G_Dashboard] ([C_CO], [D_CO], [Total], [A_RG], [B_RG], [C_RG], [D_RG], [E_RG], [DateHour], [orderId]) 
VALUES (N'710', N'CGL', N'4', N'3', N'0', N'1', N'0', N'0', N'2025-05-26 17:51:00.000', N'1')

INSERT INTO [dbo].[G_Dashboard] ([C_CO], [D_CO], [Total], [A_RG], [B_RG], [C_RG], [D_RG], [E_RG], [DateHour], [orderId]) 
VALUES (N'810', N'PLR', N'8', N'8', N'0', N'0', N'0', N'0', N'2025-05-26 17:51:00.000', N'2')

INSERT INTO [dbo].[G_Dashboard] ([C_CO], [D_CO], [Total], [A_RG], [B_RG], [C_RG], [D_RG], [E_RG], [DateHour], [orderId]) 
VALUES (N'830', N'CTN', N'10', N'10', N'0', N'0', N'0', N'0', N'2025-05-26 17:51:00.000', N'2')

INSERT INTO [dbo].[G_Dashboard] ([C_CO], [D_CO], [Total], [A_RG], [B_RG], [C_RG], [D_RG], [E_RG], [DateHour], [orderId]) 
VALUES (N'E10', N'BLG', N'2', N'1', N'0', N'1', N'0', N'0', N'2025-05-26 17:51:00.000', N'3')

INSERT INTO [dbo].[G_Dashboard] ([C_CO], [D_CO], [Total], [A_RG], [B_RG], [C_RG], [D_RG], [E_RG], [DateHour], [orderId]) 
VALUES (N'E40', N'MDN', N'2', N'1', N'0', N'1', N'0', N'0', N'2025-05-26 17:51:00.000', N'3')

需要生成包含明细、前缀小计、总计的结果集：

原查询因ROLLUP分组逻辑错误，无法得到预期结果：

SELECT 
    LEFT(C_CO, 1) C,
    C_CO,
    orderid,
    SUM(Total) AS SalesTotal 
FROM
    [dbo].[G_Dashboard] 
GROUP BY
    C_CO,
    ROLLUP(LEFT(C_CO, 1), orderid);

解决方案 #

使用ROLLUP定义正确的分组层级，结合GROUPING函数判断汇总层级，同时通过CASE语句格式化显示内容：

SELECT
    -- 处理C列：总计行显示空，其他行显示C_CO首字符
    CASE 
        WHEN GROUPING(LEFT(C_CO, 1)) = 1 THEN ''
        ELSE LEFT(C_CO, 1) 
    END AS C,
    -- 处理C_CO列：小计行显示C_CO首字符，明细行显示原C_CO值
    CASE 
        WHEN GROUPING(C_CO) = 1 THEN LEFT(MAX(C_CO), 1)
        ELSE C_CO 
    END AS C_CO,
    -- 处理orderid列：总计行显示'total'，小计行显示'subtotal'，明细行显示orderid值
    CASE 
        WHEN GROUPING(LEFT(C_CO, 1)) = 1 THEN 'total'
        WHEN GROUPING(C_CO) = 1 THEN 'subtotal'
        ELSE CAST(orderid AS VARCHAR(10)) 
    END AS orderid,
    SUM(Total) AS SalesTotal
FROM [dbo].[G_Dashboard]
-- 定义ROLLUP层级：先按C_CO首字符，再按orderid，最后按C_CO
GROUP BY ROLLUP(LEFT(C_CO, 1), orderid, C_CO)
-- 过滤掉不需要的中间汇总层级（仅保留明细、前缀小计、总计）
HAVING GROUPING(orderid) = 0 OR GROUPING(C_CO) = 1
-- 按预期顺序排序
ORDER BY 
    -- 总计行放最后
    CASE WHEN GROUPING(LEFT(C_CO, 1)) = 1 THEN 1 ELSE 0 END,
    LEFT(C_CO, 1),
    -- 小计行放在对应前缀的明细行之后
    CASE WHEN GROUPING(C_CO) = 1 THEN 1 ELSE 0 END,
    C_CO;

关键说明 #

1. ROLLUP层级定义：ROLLUP(LEFT(C_CO, 1), orderid, C_CO)会生成三级汇总：
   • 明细层级：LEFT(C_CO,1) + orderid + C_CO → 对应每条原始数据行
   • 前缀小计层级：LEFT(C_CO,1) → 汇总同一首字符下的所有Total
   • 总计层级：无分组列 → 汇总所有行的Total
2. GROUPING函数：用于判断当前行是否为对应列的汇总行（返回1表示是汇总行，0表示不是），以此来格式化显示文本。
3. HAVING过滤：排除LEFT(C_CO,1)+orderid的中间汇总层级，只保留需要的明细、前缀小计和总计。
4. 排序逻辑：确保总计行在最后，小计行在对应前缀的明细行之后，符合预期的结果顺序。

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