使用 GROUP BY 的 WITH ROLLUP 字句可以检索出更多的分组聚合信息,它不仅仅能像一般的 GROUP BY 语句那样检索出各组的聚合信息,还能检索出本组类的整体聚合信息。RollUp是上卷功能,类似于数据挖掘中的上卷操作。ROLLUp的功能和Order by功能是互斥的。
GROUPBY子句允许使用WITH ROLLUP,从而使额外的行被添加到输出的结果。这些行代表上级(或超总量)汇总操作。ROLLUP使你可以在一个单一的查询中统计多种数据。它可以使用在例如,OLAP(在线分析处理)操作。
下面我们的例子对比了普通的 GROUP BY 操作和有 WITH ROLLUP 子句的 GROUP BY 操作的不同:
CREATE TABLE sales
(
year INT NOT NULL,
country VARCHAR(20) NOT NULL,
product VARCHAR(32) NOT NULL,
profit INT
);
对年进行分类操作,统计每年的总费用
mysql> SELECT year, SUM(profit) FROM sales GROUP BY year;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 | 4525 |
| 2001 | 3010 |
+------+-------------+
如果我们还相爱那个在统计每一年的总费用的同时,在统计所有年的总费用,则我们仅仅只需要添加with roll即可
mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 | 4525 |
| 2001 | 3010 |
| NULL | 7535 |
+------+-------------+
当GROUP BY有多个组列时,ROLLUP有更复杂的效果。在这种情况下,每个GROUP BY都有的属性都会产生一个“中断”(值的变化),除了在最后一个分组列不产生中断,最后查询产生一个额外的超级汇总汇总行。
举例如下:
mysql> SELECT year, country, product, SUM(profit)
-> FROM sales
-> GROUP BY year, country, product;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2001 | Finland | Phone | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
如果使用了 ROLLUP 语句,则会产生额外的几行数据:
mysql> SELECT year, country, product, SUM(profit)
-> FROM sales
-> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | NULL | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | NULL | 1575 |
| 2000 | NULL | NULL | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | NULL | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | NULL | 3000 |
| 2001 | NULL | NULL | 3010 |
| NULL | NULL | NULL | 7535 |
+------+---------+------------+-------------+
对于这个查询语句,添加ROLLUP 使输出包括四个层次的分析汇总信息,不只是一个。这里是如何解释汇总输出:
1、在给定year 和country的每一组汇总行之后,将产生一个额外的汇总行,显示所有product的总数。这些行有将product列设置为空,计算的是当前的year 和country对应的product的总数。
2、在给定的一年中的每一行之后,将产生一个额外的汇总行,显示所有country和product的总数。这些行将country和product列设置为空,计算的是当前的year 对应的总数。
3、最后,所有其他行,一个额外的汇总行产生显示总为所有year ,country和product。这一行将这三列都设置为空,即计算的是所有的总数。