rollup
rollup是 SQL 关键字，在 MySQL 中得用with rollup。它是group by子句的扩展，用于统计后增加一行汇总数据。
举例，现有库存表，我们按仓库名称分组，统计每个仓库的产品总量，最后来一个汇总。
mysql> SELECT * FROM inventory;
+----+---------------+---------+----------+
| id | warehouse | product | quantity |
+----+---------------+---------+----------+
| 1 | San Jose | iPhone | 100 |
| 2 | San Fransisco | iPhone | 60 |
| 3 | San Jose | huawei | 200 |
| 4 | San Fransisco | huawei | 100 |
+----+---------------+---------+----------+
SELECT warehouse, SUM(quantity) sum
FROM inventory
GROUP BY warehouse WITH ROLLUP;
+---------------+------+
| warehouse | sum |
+---------------+------+
| San Fransisco | 160 |
| San Jose | 300 |
| NULL | 460 |
+---------------+------+
最后，使用COALESCE()函数将NULL值替换成一个别名。COALESCE()的功能是返回第一个不为空的值。
SELECT COALESCE(warehouse, 'warehouseAll') warehouse, SUM(quantity) sum
FROM inventory
GROUP BY warehouse WITH ROLLUP;
+---------------+------+
| warehouse | sum |
+---------------+------+
| San Fransisco | 160 |
| San Jose | 300 |
| warehouseAll | 460 |
+---------------+------+