MySQL中的GROUP BY修饰符WITH ROLLUP

MYSQL中的GROUP BY可以添加WITH ROLLUP 修饰符，ROLLUP含义是卷起，在MYSQL中是进行统计格外的汇总信息。WITH ROLLUP可以让你在一个QUERY中进行更多LEVEL的数据分析，例如表结构：

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

为了查看每一年的利润，可以执行SQL:

       SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year;

结果：

+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

但是如果你还需要看所有年份的总利润的话，可能还需要格外的SUM计算，其实不用那么麻烦，WITH ROLLUP 就可以帮你解决此问题：

       SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;

查询结果：

+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
| NULL |   7535 |
+------+--------+

其中year为NULL的则是近两年的所有利润，是不是很便捷！！！除次之外WITH ROLLUP还可以按照多个列进行汇总，例如：

       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 |
+------+---------+------------+--------+

可以从各个维度进行汇总分析。

对于WITH ROLLUP 的使用约束参考：Other Considerations When using ROLLUP

 

以上所有总结参考MYSQL官网：https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html