VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
GROUP BY 给您每组 1 行,但没有整体的那一行。WITH ROLLUP 补上这一行,当您按多列分组时,还补上中间的小计。它省去了再运行一个查询、自己把数字加起来的工夫。
开始之前,请用 mysql -u root -p sakila 打开客户端。
WITH ROLLUP 的工作原理
GROUPING_COLUMN 替换为定义分组的列,TABLE_NAME 替换为表,AGGREGATE_FUNCTION(COLUMN_NAME) 替换为每组要计算的内容。WITH ROLLUP 放在 GROUP BY 子句的末尾。
服务器返回正常的分组,然后是额外的行,在这些行中某个分组列被搁置,聚合则涵盖全部行。它在被搁置的列中放 NULL,您就是靠这个认出那些行的。
示例
- 添加总计行
- 还有小计
按 2 列分组,您得到第一列每个值的小计,然后是总计。
G / NULL / 3 是 G 在所有价格上的小计。NULL / NULL / 10 是总计,与时长 185 分钟的 10 部影片一致。
汇总行紧跟在它们所汇总的分组之后,而不是集中在底部,所以结果从上到下读起来像一份报表。
区分汇总 NULL 与真正的 NULL
汇总行中的 NULL 表示”这一列的每个值”。数据中的 NULL 表示”没有记录值”。它们打印出来一模一样,所以在一个可以为 NULL 的列上,您无法靠看来区分这 2 种。GROUPING() 直接回答这个问题。当该列在这一行中被搁置时它返回 1,否则返回 0。
rating 在 Sakila 中从不为 NULL,所以这里这 2 个问题的答案相同。在一个确实含有 NULL 的列上,测试 rating IS NULL 会抓到两种行,而 GROUPING(rating) = 1 只抓到汇总行。
ORDER BY 会移动总计,并改变排序
上面的示例没有ORDER BY,这是有意的:WITH ROLLUP 把每个汇总行直接放在它所汇总的分组下面,而排序会把这种排列丢掉。
ENUM 声明顺序,因为对汇总结果排序是把它当作文本来排的。
所以请决定您要哪一种。想要一份小计位于各组之下的报表,就不加 ORDER BY;或者加上它,并接受汇总行落在排序把它们放到的任何位置。
给汇总行加标签
一列 NULL 不是您在报表中想要的。COALESCE 返回它第一个不为 NULL 的参数,所以它可以在那里放一个词。
rating 从不为 NULL。在一个可以为 NULL 的列上,COALESCE 会把真正的空缺标记为总计,所以请改用 GROUPING() 来做测试。
总结
- 在
GROUP BY的末尾加上WITH ROLLUP,得到小计行和总计行。 - 把分组列中的 NULL 读作”这一列的每个值”。
- 按多列分组时,预期前面每一列的每个值都有一个小计。
- 用
GROUPING(column)把汇总行与数据为 NULL 的行区分开。 - 预期
ORDER BY会移动汇总行,并把ENUM当作文本排序。
另请参阅
- GROUP BY —
WITH ROLLUP为其添加总计的那些分组 - 聚合函数 — 被加总的东西
- IS NULL — 那一列中的 NULL 可能有的另一种含义
- MySQL GROUP BY 性能 — 大规模分组的代价