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 |
<span style="font-weight: bold; font-size: 9.75pt; colo