mysql group by with rollup

mysql group by with rollup

1、普通的 GROUP BY 操作，可以按照部門和職位進行分組，計算每個部門，每個職位的工資平均值：
mysql> select dep,pos,avg(sal) from employee group by dep,pos;
+------+------+-----------+
| dep | pos | avg(sal) |
+------+------+-----------+
| 01 | 01 | 1500.0000 |
| 01 | 02 | 1950.0000 |
| 02 | 01 | 1500.0000 |
| 02 | 02 | 2450.0000 |
| 03 | 01 | 2500.0000 |
| 03 | 02 | 2550.0000 |
+------+------+-----------+
6 rows in set (0.02 sec)

2、如果我們希望顯示部門的平均值和全部雇員的平均值，普通的 GROUP BY 語句是不能實現的，需要另外執行一個查詢操作，或者通過程序來計算。如果使用有 WITH ROLLUP 子句的 GROUP BY 語句，則可以輕松實現這個要求：
mysql> select dep,pos,avg(sal) from employee group by dep,pos with rollup;
+------+------+-----------+
| dep | pos | avg(sal) |
+------+------+-----------+
| 01 | 01 | 1500.0000 |
| 01 | 02 | 1950.0000 |
| 01 | NULL | 1725.0000 |
| 02 | 01 | 1500.0000 |
| 02 | 02 | 2450.0000 |
| 02 | NULL | 2133.3333 |
| 03 | 01 | 2500.0000 |
| 03 | 02 | 2550.0000 |
| 03 | NULL | 2533.3333 |
| NULL | NULL | 2090.0000 |
+------+------+-----------+
10 rows in set (0.00 sec)