MySQL 4.1.1 与后续的版本支持WITH ROLLUP修饰符(modifier),可以统计输出的集合字段。
查找每年的葡萄酒销售总额:
SELECT year,sum(price) FROM wine
INNER JOIN items ON wine.id = items.wine_id
GROUP BY year;
部分查询结果:
year sum(price)
1980 2056.12
1981 16234.78
...
2007 18000.12
2008 20183.34
若在查询中加上WITH ROLLUP
SELECT year,sum(price) FROM wine
INNER JOIN items ON wine.id = items.wine_id
GROUP BY year
WITH ROLLUP;
查询结果会多一行记录:
year sum(price)
1980 2056.12
1981 16234.78
...
2007 18000.12
2008 20183.34
NULL 5893233.21
多出的这一行,年份显示null,sum(price)是销售额的总计,如此处理便可省去如下代码的运行:
SELECT sum(price) FROM wine
INNER JOIN items ON wine.id = items.wine_id;
找出每种葡萄酒的销售总额:
在查找过程中,加入了产酒区(region)、酒厂表(winery),便可使用WITH ROLLUP 取得各个酒厂与产酒区的销售金额
关键:在GROUP BY子句中使用region与winery表的唯一值。
SELECT region_name,winery_name,wine_id,sum(price) FROM region
INNER JOIN winey ON region.id = winery.region_id
INNER JOIN wine ON wine.winery_id = winery.id
INNER JOIN items ON wine.id = items.wine_id
GROUP BY region_name,winery_name,wine.id
WITH ROLLUP;
部分查询结果:
region_name winery_name wine_id sum(price)
barissa anderson wines 112 682.3
barissa anderson wines 113 732.4
barissa anderson wines 114 1123.3
barissa anderson wines 115 68.1
barissa anderson wines NULL 2001.8
...
barissa NULL NULL 698403.5
...
NULL NULL NULL 4287403.6
第六行显示anderson wines酒厂的总销售额(前五行的和),同样其他酒厂的总销售额都会列在该酒厂数据的后面,barissa地区的所有酒厂查询完毕后,barissa地区的总金额会出现在winery_name 和wine_id均为null的那一行,最后一行,是所有的地区、酒厂、酒品种销售额的总和。
WITH ROLLUP特点:
- ORDER BY 子句不能和WITH ROLLUP 同时使用;
- 输出产生后才会运用LIMIT子句,所以总会包含总计是NULL的数据行。