Mysql的with rollup功能（5.1以上版本）

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

?