如何在SQL中通过ROLLUP与CUBE生成层次化小计总计-SQL-PHP中文网

ROLLUP生成树状层级小计，按GROUP BY列从左到右逐级归并，如ROLLUP(a,b,c)产生(a,b,c)、(a,b)、(a)、()四层，不支持跨级组合；需用GROUPING()函数区分占位NULL与真实NULL。

如何在sql中通过rollup与cube生成层次化小计总计

ROLLUP 生成的是树状层级小计，不是简单叠加

ROLLUP 按字段顺序构建「从左到右递减」的分组层次，比如 GROUP BY ROLLUP(a, b, c) 实际等价于四个分组组合：(a,b,c)、(a,b)、(a)、()（全表总计）。它不会生成 (b,c) 或 (c) 这类跳过前序字段的组合——这点常被误认为“漏算”，其实是设计使然。

常见错误是把 ROLLUP(a, b) 当成“先按 a 小计、再按 b 小计”，结果发现 b 的小计行里 a 值为 NULL 却没对应 b 的独立汇总。这是因为 ROLLUP 不支持跨字段重排，想让 b 成为主层级，得写成 ROLLUP(b, a)。

ORDER BY 后不能直接用别名引用 ROLLUP 生成的 NULL 行，需用 GROUPING() 函数判断层级，例如 ORDER BY GROUPING(dept), dept
MySQL 8.0+ 和 PostgreSQL 9.5+ 支持 ROLLUP；SQLite 支持但不支持 GROUPING()，只能靠 IS NULL 粗略识别
ROLLUP 结果中，某列值为 NULL 不一定代表该层小计——需配合 GROUPING(col) 返回 1 才能确认是系统填充的占位符

CUBE 生成所有维度组合，但行数爆炸风险高

CUBE(a, b, c) 会枚举所有 2³ = 8 种非空子集：包括 (a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()。它比 ROLLUP 更“全”，但也更难读——尤其当字段语义不正交时（比如同时对 region 和 sales_rep 做 CUBE），会出现大量无业务意义的交叉小计行。

性能上，CUBE 的中间结果集大小随维度数指数增长。3 个字段最多 8 行，5 个字段就达 32 行，实际查询可能因物化临时表拖慢数倍。线上报表慎用超过 4 个字段的 CUBE。

SQL Server 和 Oracle 默认支持 CUBE；PostgreSQL 需开启 standard_conforming_strings = on 并用标准语法，MySQL 8.0+ 才支持
用 GROUPING_ID(a,b,c) 可一次性获取组合标识码，比嵌套多个 GROUPING() 更简洁
若只需部分组合（如排除 (b,c)），CUBE 不适用，应改用 UNION ALL 手动拼接多个 GROUP BY

GROUPING() 是区分真实 NULL 和小计占位符的唯一可靠方式

原始数据里某列本就是 NULL，和 ROLLUP/CUBE 自动生成的小计行里该列为 NULL，在结果集中完全无法视觉分辨。只靠 IS NULL 判断会误标真实数据，导致小计逻辑错乱。

GROUPING(dept) 在 dept 是小计占位符时返回 1，真实数据为 NULL 时返回 0。这是 SQL 标准定义的行为，所有支持 ROLLUP/CUBE 的引擎都遵循。

不要写 CASE WHEN dept IS NULL THEN 'Total' ELSE dept END —— 它会把真实部门名 NULL 也标成 Total
正确写法是 CASE WHEN GROUPING(dept) = 1 THEN 'Dept Total' ELSE dept END
多个字段时，GROUPING(dept, region) 返回一个位掩码整数，需用 GROUPING(dept) = 1 AND GROUPING(region) = 1 显式判断更清晰

替代方案：用 GROUPING SETS 精确控制小计组合

当 ROLLUP 太死板、CUBE 又太泛滥时，GROUPING SETS 是更可控的选择。它允许显式列出需要的分组组合，比如 GROUP BY GROUPING SETS ((dept), (region), (dept, region), ()) 就只生成部门小计、大区小计、部门×大区明细、全表总计四类，跳过无关组合。

它的代价是写法略冗长，但换来的是确定性。某些场景下，用 UNION ALL 手动合并几个独立 GROUP BY 查询反而更易维护、更容易加索引提示或分区裁剪。