MySQL WITH ROLLUP 怎样生成小计和总计

MySQL 要在分组明细后同时生成小计和总计，可以在 GROUP BY 末尾加上 WITH ROLLUP。单列分组会多出总计行；多列分组则会按照列的先后顺序逐级生成小计，最后再生成总计。汇总行中的分组列通常显示为 NULL，如果源数据本身也允许 NULL，应配合 GROUPING() 判断它是不是 ROLLUP 产生的汇总值。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/group-by-modifiers.html

先用单列 WITH ROLLUP 生成总计

假设销售数据按年份统计利润。普通 GROUP BY 只会返回每个年份的聚合结果；在末尾加上 WITH ROLLUP 后，MySQL 会再追加一行跨越全部年份的超聚合结果。

-- 建立一个只用于演示分组层级的临时表
CREATE TEMPORARY TABLE sales_summary (
    sale_year INT NOT NULL,
    country VARCHAR(32) NOT NULL,
    product VARCHAR(32) NOT NULL,
    profit DECIMAL(12, 2) NOT NULL
);

-- 按年份统计，并在末尾追加所有年份的总计
SELECT sale_year,
       SUM(profit) AS total_profit
FROM sales_summary
GROUP BY sale_year WITH ROLLUP;

查询返回的最后一行中，sale_year 会显示为 NULL，total_profit 则是所有年份利润之和。这里的 NULL 不是某条销售记录缺少年份，而是 MySQL 用来表示“所有年份”的汇总标记。

MySQL 8.4 文档还给出了等价的 GROUP BY ROLLUP (sale_year) 写法。文章后面的多列示例使用更常见的 GROUP BY ... WITH ROLLUP，这样更容易看出汇总层级。

按分组列顺序生成多级小计

多列 GROUP BY 时，列的顺序就是汇总层级。下面按“年份、国家、产品”分组：最细粒度是年份+国家+产品，产品列被汇总后形成国家内的小计；继续汇总国家和产品形成年度小计；最后三列都进入汇总行，得到总计。

-- 三列从左到右定义汇总层级：年份 -> 国家 -> 产品
SELECT sale_year,
       country,
       product,
       SUM(profit) AS total_profit
FROM sales_summary
GROUP BY sale_year, country, product WITH ROLLUP;

可以把结果理解成四个层级：

因此，想把“国家小计”放在别的业务维度上，不能只调整展示文案，而要重新排列 GROUP BY 列。ROLLUP 是沿着分组列顺序产生前缀层级，不是任意组合所有维度。

用 GROUPING() 区分汇总 NULL

如果业务字段允许真实的 NULL，仅看结果中的 NULL 就不够了。例如，某个商品没有录入产品名时，普通分组也可能出现 product IS NULL 的明细聚合行。MySQL 提供 GROUPING(product) 来区分这两种情况：普通分组返回 0，ROLLUP 产生的超聚合列返回 1。

-- 同时输出每个维度是否处于 ROLLUP 汇总层级
SELECT sale_year,
       country,
       product,
       SUM(profit) AS total_profit,
       GROUPING(sale_year) AS is_year_total,
       GROUPING(country) AS is_country_total,
       GROUPING(product) AS is_product_total
FROM sales_summary
GROUP BY sale_year, country, product WITH ROLLUP;

例如，某行的 product 是 NULL 且 is_product_total=0，说明它可能是源数据中的真实空值；如果 is_product_total=1，则说明这一行是产品维度被汇总后的产品小计。这个判断也适用于国家和年份列。

给汇总行加标签并筛选

直接显示 NULL 对报表用户不够友好，可以在查询列中根据 GROUPING() 替换成“小计”或“总计”。保留普通数据的真实 NULL，只替换函数返回 1 的汇总值。

-- 只给 ROLLUP 产生的空维度加标签，保留业务数据里的真实 NULL
SELECT IF(GROUPING(sale_year) = 1, '全部年份', CAST(sale_year AS CHAR)) AS year_label,
       IF(GROUPING(country) = 1, '全部国家', COALESCE(country, '未填写国家')) AS country_label,
       IF(GROUPING(product) = 1, '全部产品', COALESCE(product, '未填写产品')) AS product_label,
       SUM(profit) AS total_profit
FROM sales_summary
GROUP BY sale_year, country, product WITH ROLLUP;

如果只想查看小计和总计，可以用多参数形式的 GROUPING() 在 HAVING 中筛选。只要任意一个分组维度进入了汇总层级，组合结果就不为 0。

-- 过滤掉最细粒度明细，只保留产品小计、国家小计和总计
SELECT sale_year,
       country,
       product,
       SUM(profit) AS total_profit
FROM sales_summary
GROUP BY sale_year, country, product WITH ROLLUP
HAVING GROUPING(sale_year, country, product)  0;

这里的多参数结果是一个位掩码，右侧参数对应低位。实际写报表时，如果只需要某一级，分别检查目标列的 GROUPING() 值通常更直观，例如只保留产品小计可以使用 GROUPING(product)=1 AND GROUPING(country)=0。

处理排序、WHERE、HAVING 与 LIMIT 边界

ROLLUP 的汇总行是在分组聚合之后形成的，几个常见条件要放在正确位置。

-- WHERE 先缩小原始数据范围，再对过滤后的数据生成小计和总计
SELECT sale_year,
       country,
       product,
       SUM(profit) AS total_profit
FROM sales_summary
WHERE sale_year >= 2025
GROUP BY sale_year, country, product WITH ROLLUP
-- HAVING 可以识别已经生成的 ROLLUP 行
HAVING GROUPING(sale_year, country, product)  0
-- 将总计放到结果前面；明细排序规则可按报表需要继续补充
ORDER BY GROUPING(sale_year) DESC, sale_year;

• WHERE 过滤源表行，适合限定年份、租户或业务范围；它不能用来判断 ROLLUP 后才出现的汇总 NULL。
• HAVING 位于聚合之后，可以使用 GROUPING() 选择小计或总计。
• ORDER BY GROUPING(sale_year) DESC 可以把总计行排在前面；不写排序时，不要把默认返回顺序当成业务契约。
• LIMIT 作用于已经包含 ROLLUP 行的结果，截断时可能把一个汇总层级切开，报表分页要特别谨慎。

如果只是需要一个总数，而不需要明细、小计和总计同时返回，单独执行聚合查询通常更容易维护。ROLLUP 更适合一次性生成层级报表，或把多级汇总交给同一个 SQL 结果集。

相关问题

WITH ROLLUP 和 GROUP BY ROLLUP() 有什么区别？

在 MySQL 8.4 的文档示例中，两者都是 ROLLUP 修饰语的写法。日常维护时选团队更熟悉的一种，并保持分组列顺序一致。

为什么 ROLLUP 的汇总行显示为 NULL？

MySQL 用分组列上的 NULL 表示该维度已经被汇总。若源数据也可能为 NULL，应使用 GROUPING() 判断它是不是超聚合标记。

可以用 WHERE product IS NULL 只取小计吗？

不建议这样做。ROLLUP 的汇总 NULL 在较晚阶段写入结果，且真实数据也可能为 NULL；使用 HAVING GROUPING(product)=1 才能明确筛选产品小计。

多列 ROLLUP 为什么会多出很多行？

每个分组列前缀都会形成一个汇总层级。分组列越多，层级越多；应先确认报表需要的维度顺序，再决定是否使用 ROLLUP。