SQL中去除COALESCE分组结果的重复记录并保持原有排序

问题原因 #

原查询的GROUPING SETS包含了(Category)和(SubCate, Category)两个分组规则。对于Human这条无子类别的数据，两个分组都会生成ALL_CATEGORY为Human的统计结果——因为COALESCE(SubCate, Category)在两种分组下都会返回Human，最终导致重复记录。而直接用DISTINCT会破坏原有的排序逻辑，因为DISTINCT的执行顺序在排序之前。

修正后的SQL语句 #

首先注意原SQL里的字段名错误：表结构是SubCate，原查询写成了SubCategory，需要先修正。然后通过GROUPING()函数区分分组类型，筛选掉不需要的重复记录：

SELECT COALESCE(SubCate, Category) AS ALL_CATEGORY, SUM(Value) AS VALUE
FROM TEMP_TEST
GROUP BY GROUPING SETS ( (Category), (SubCate, Category) )
-- 核心筛选逻辑：当SubCate为空时，只保留按Category单独分组的记录
HAVING CASE 
         WHEN SubCate IS NULL THEN GROUPING(SubCate) = 1 
         ELSE TRUE 
       END
ORDER BY Category, SubCate;

逻辑说明 #

• GROUPING(SubCate)函数会返回1表示当前分组未包含SubCate（也就是按Category单独分组的情况），返回0表示分组包含SubCate（按SubCate+Category分组的情况）。
• 对于有SubCate的行，两种分组的结果都需要保留（子类别的明细和类别的汇总）；对于无SubCate的行，只保留按Category分组的汇总记录，避免重复。

最终输出结果 #

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