Mysql的with rollup功能（5.1以下版本）
日期：2014-05-16 浏览次数：20853 次
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 |
