如何修改SQL Server查询以添加TargetCategory小计与全局总计？

嘿，这个需求用SQL Server的ROLLUP特性就能完美解决，它专门用来生成层级化的汇总统计（比如分组小计、总计），比手动union多个查询要高效得多，而且代码更简洁易维护。

核心思路 #

原查询是按EventID、TargetCategory、RoundName分组统计未完成事件，我们需要调整分组逻辑，让ROLLUP按照TargetCategory → RoundName → EventID的层级自动生成：

1. 原始的明细行（每个EventID的统计）
2. 每个TargetCategory下的小计行
3. 整个结果集的总计行

同时用GROUPING()函数识别汇总行，给这些行添加友好的标签（比如“XX分类小计”“总计”），让结果更易读。

修改后的完整查询 #

SELECT 
    -- 处理EventID列的显示：汇总行显示对应标签，明细行显示原ID
    CASE 
        WHEN GROUPING(BCD.[EventID]) = 1 AND GROUPING(BCD.[RoundName]) = 1 THEN '总计'
        WHEN GROUPING(BCD.[EventID]) = 1 THEN CONCAT(M.TargetCategory, ' 小计')
        ELSE CAST(BCD.[EventID] AS VARCHAR(50))
    END AS [EventID],
    -- TargetCategory列：总计行显示“所有分类”，其余显示原分类名
    CASE 
        WHEN GROUPING(M.TargetCategory) = 1 THEN '所有分类'
        ELSE M.TargetCategory
    END AS [TargetCategory],
    -- RoundName列：汇总行留空，明细行显示原名称
    CASE 
        WHEN GROUPING(BCD.[RoundName]) = 1 THEN ''
        ELSE BCD.[RoundName]
    END AS [RoundName],
    -- 保持原有的未完成事件统计逻辑
    COUNT(CASE WHEN R.Status_Id <> 4 THEN 1 ELSE NULL END) AS [Outstanding Events]
FROM [dbo].[Event_Details] BCD 
LEFT JOIN [dbo].[lkpTarget] M ON BCD.TargetID = M.TargetID 
LEFT JOIN [dbo].[EventComments] R ON BCD.EventID = R.[EventID] 
-- 使用ROLLUP定义层级分组：先分类，再轮次，最后事件ID
GROUP BY ROLLUP(M.TargetCategory, BCD.[RoundName], BCD.[EventID])
-- 过滤掉未完成事件为0的行（和原查询逻辑一致，汇总行也需满足该条件）
HAVING COUNT(CASE WHEN R.Status_Id <> 4 THEN 1 ELSE NULL END) > 0 
-- 排序：先按分类，分类小计在该分类明细之后，总计行最后
ORDER BY 
    CASE WHEN GROUPING(M.TargetCategory) = 1 THEN 1 ELSE 0 END,
    M.TargetCategory ASC,
    CASE WHEN GROUPING(BCD.[RoundName]) = 1 THEN 1 ELSE 0 END,
    BCD.[RoundName] ASC,
    BCD.[EventID] ASC;

关键细节说明 #

1. ROLLUP的作用：
   它会自动生成三组分组结果：

   • 全维度分组：(TargetCategory, RoundName, EventID) → 原始明细行
   • 半维度分组：(TargetCategory) → 分类小计行
   • 空维度分组：() → 总计行

2. GROUPING()函数：
   用来判断当前行是否是某列的汇总行：返回1表示该行是该列的汇总行，返回0表示是明细行。我们用它来给汇总行设置友好的显示文本。

3. 替代方案：GROUPING SETS
   如果你想更明确地指定需要哪些分组（而不是依赖层级），可以用GROUPING SETS替代ROLLUP，效果完全一致：

   GROUP BY GROUPING SETS(
       (M.TargetCategory, BCD.[RoundName], BCD.[EventID]), -- 明细行
       (M.TargetCategory), -- 分类小计
       () -- 总计
   )
   

这个方案的优势是只需要一次查询，比多次union查询性能更好，而且逻辑清晰，后续维护也更方便。

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