MySQL WithRollup 学习记录一

The GROUP BY clause permits a WITH ROLLUP modifier that causes summary output to include extra rows that represent higher-level (that is, super-aggregate) summary operations.

GROUP BY 子句加上 WITH ROLLUP 可以在正常的groupby分组的结果集上添加一个更高一级的摘要/总计 之类的行。

ROLLUP thus enables you to answer questions at multiple levels of analysis with a single query. For example, ROLLUP can be used to provide support for OLAP (Online Analytical Processing) operations.

ROLLUP给我们提供了一种方法可以在一条查询SQL语句中实现不同层级的摘要/总计。下面假设我们有如下一张销售包含的字段有：year, country, product, 和 profit 。

CREATE TABLE sales
(
    year    INT,
    country VARCHAR(20),
    product VARCHAR(32),
    profit  INT
);

当我们需要按年显示销售利润总和时，我们需要用到GROUP BY子句，然后我们就得到下面的结果集：

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

上面的结果只是列出了按年结算的利润总和。如果我们要算出所有年份的利润总和，这是不得不使用其他方式或者使用额外的SQL语句来对这个结果集进行求和。在这种情况下我们可以使用ROLLUP来实现这一需求。代码如下所示：

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
| NULL |   7535 |
+------+--------+

最后一行NULL | 7537 就是所有年份的利润总和（例子数据中只有两行数据）。

如果GROUPBY 子句中包含多个列的话，ROLLUP会呈现出更复杂的一种情况。下面我们来做个对比：

首先我们只用GROUP BY来查询

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product;
+------+---------+------------+--------+
| year | country | product    | 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 |
+------+---------+------------+--------+

结果集中时按year、country、product进行的分组求和。

再来看看使用了ROLLUP以后会是个什么样子

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+
| year | country | product    | 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以后，在原有的分组计算的结果集上有更进一部的按年、国家、产品求出了总和，还有按年部分国家和产品也求出了总和，更甚至求出了所有年不分国家和产品的总的利润之和。