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

来源：互联网发布：被雷劈死的国师知乎编辑：程序博客网时间：2024/05/19 13:57

原文链接：http://jbm3072.iteye.com/blog/1168429

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

===============================================================

简单来说就是会自己加一个汇总。

0 0