SQL如何实现带有层级结构的分类汇总_利用ROLLUP或CUBE生成多级小计-SQL-PHP中文网

rollup按group by列顺序从左到右逐级归并生成树状汇总，如group by a,b,c产生(a,b,c)、(a,b,null)、(a,null,null)、(null,null,null)四层，不支持跨级组合；cube则生成所有2ⁿ种组合，适合多维任意交叉分析但易致结果集爆炸；grouping()函数用于精准识别汇总行中的占位null，避免与真实null混淆。

sql如何实现带有层级结构的分类汇总_利用rollup或cube生成多级小计

ROLLUP生成层级小计时，GROUP BY顺序决定汇总路径

ROLLUP不是简单地“把所有组合都算一遍”，它严格按GROUP BY子句中列的书写顺序，从左到右逐级向上归并。比如GROUP BY a, b, c WITH ROLLUP会产出：(a,b,c)、(a,b,NULL)、(a,NULL,NULL)、(NULL,NULL,NULL) 四层结果，但不会出现(NULL,b,c)或(a,NULL,c)——这些在逻辑上不构成树状父-子关系。

常见错误是把高粒度字段（如product_id）写在前面，导致顶层小计变成“全部产品总和”，而漏掉中间层（如“每个品类的总和”）。正确做法是按业务层级由粗到细排列：先category，再subcategory，最后product。

想看“部门 → 小组 → 员工”三级汇总？必须写GROUP BY dept, team, emp WITH ROLLUP
如果反过来写GROUP BY emp, team, dept，ROLLUP只会生成员工级、空团队、空部门三层，失去小组维度的小计
MySQL 8.0+ 支持ROLLUP与GROUPING()配合，用GROUPING(dept)判断该行是否为dept小计行（返回1），避免把真实NULL值误判为汇总行

CUBE适合需要任意维度交叉分析的场景，但结果集容易爆炸

CUBE生成的是所有可能的列组合（2ⁿ种），不像ROLLUP只走一条主路径。例如GROUP BY a, b, c WITH CUBE会产生8行：包括(a,b,c)、(a,b,NULL)、(a,NULL,c)、(NULL,b,c)、(a,NULL,NULL)、(NULL,b,NULL)、(NULL,NULL,c)、(NULL,NULL,NULL)。

这意味着它天然支持“切片”（固定某列值查其他维度）和“钻取”（比如从大区下钻到城市再下钻到门店），但代价明显：3个字段就8组，5个字段就是32组，数据量和查询耗时会指数增长。

报表需求明确要“销售额按年+月+地区+产品线任意组合查看”？CUBE比手写多个UNION更简洁
但若只是常规的“年→季度→月”时间层级，用ROLLUP更可控，CUBE会多出(年,NULL,地区)这类无业务意义的组合
PostgreSQL需用GROUPING SETS模拟CUBE行为；SQL Server和MySQL 8.0+原生支持WITH CUBE，但MySQL对NULL处理较弱，建议搭配GROUPING()函数过滤

用GROUPING()识别汇总行，避免把真实NULL当成小计

ROLLUP/CUBE产生的NULL是占位符，不代表原始数据里真有NULL值。如果原始表中region字段本身就允许NULL，仅靠IS NULL无法区分这是“华东区小计”还是“未知地区明细”。这时必须用GROUPING(region)：返回1表示该列为ROLLUP/CUBE生成的汇总占位，返回0才是真实数据。

典型写法是配合CASE WHEN构造可读标签：

SELECT
  CASE WHEN GROUPING(category) = 1 THEN '总计'
       WHEN GROUPING(subcategory) = 1 THEN CONCAT(category, '小计')
       ELSE subcategory END AS label,
  SUM(sales) AS total
FROM sales
GROUP BY category, subcategory WITH ROLLUP;

不要直接WHERE region IS NULL来筛小计行——会同时过滤掉真实为NULL的记录
Oracle和SQL Server中GROUPING()参数只能是GROUP BY里的列名；MySQL要求列名必须完全匹配，不能带别名
某些旧版MySQL（GROUPING()，此时只能靠多层子查询+窗口函数间接判断，成本高且易错

ROLLUP/CUBE在WHERE和HAVING中的行为差异很关键

WHERE在分组前过滤，HAVING在分组后过滤——这个规则对ROLLUP/CUBE依然成立，但影响更隐蔽。比如WHERE status = 'active'会剔除所有未激活的明细行，进而导致对应分组的汇总值变小；而HAVING SUM(amount) > 1000则可能把某个“部门小计”整行过滤掉，但该部门下的各小组明细仍保留。

想排除某些维度的汇总行（如不要“全国总计”，只要省市小计）？必须用HAVING GROUPING(province) = 0，不能用WHERE
ROLLUP结果中，最顶层的(NULL,NULL,NULL)行，其所有GROUPING()都为1，可用HAVING GROUPING(a)+GROUPING(b)+GROUPING(c) 排除
MySQL中，ROLLUP与ORDER BY合用时，NULL汇总行默认排在最前（ASC）或最后（DESC），若需固定顺序，得用GROUPING()参与排序，例如ORDER BY GROUPING(dept), dept

层级汇总真正难的不是语法，而是厘清业务维度间的父子关系是否严格成立——如果“产品线”和“供应商”是正交关系（一个产品可属多供应商），那ROLLUP强行套用就会产生语义混乱的“小计”，这时候应该用CUBE或显式GROUPING SETS。