RollUp是上卷功能，类似于数据挖掘中的上卷操作。
ROLLUp的功能和Order by功能是互斥的。
mysql> SELECT year,
SUM(profit) FROM sales GROUP BY year;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 | 4525 |
| 2001 | 3010
|
+------+-------------+
mysql> SELECT year,
SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 | 4525 |
| 2001 | 3010 |
| NULL | 7535 |
+------+-------------+
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
|
+------+---------+------------+-------------+
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 |
+------+---------+------------+-------------+
分享到：
相关推荐
MySQL 4.1是2004年发布的一个主要版本，引入了诸多新功能，包括增强的SQL支持，如窗口函数和WITH ROLLUP，改进的查询优化器，以及对大型数据集的支持。此外，4.1版本还增强了存储过程、触发器和视图的功能，提高了...
- 介绍了`GROUP BY WITH ROLLUP`的功能，以及如何使用它来进行汇总统计。 - **9.4 用bit group functions做统计** - 探讨了使用位组函数（如`BIT_OR`、`BIT_AND`）进行复杂统计的方法。 **第10章 其他需注意的...
CHAPTER 1 Introduction to MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.2 Database, ...