MySQL中GROUP BY修饰符ROLLUP使用

vlambda
2020-05-24

MySQL中GROUP BY修饰符ROLLUP使用

ROLLUP作用：ROLLUP子句实现在GROUP BY分组统计数据基础上再进行汇总统计。

假设有一张product_profit（产品销售利润）表，有id，product_id（产品id），sale_province（销售省份），profit（利润），sale_year（销售年份）：

CREATE TABLE `product_profit` ( `id` int(11) NOT NULL AUTO_INCREMENT, `product_id` int(11) NOT NULL COMMENT '产品id', `sale_province` varchar(32) DEFAULT NULL COMMENT '销售省份', `profit` decimal(10,2) NOT NULL COMMENT '利润', `sale_year` int(11) DEFAULT NULL COMMENT '销售年份', PRIMARY KEY (`id`)) ;

先看一下表中数据

SELECT * FROM `product_profit`;

这里要注意最后一行（id=13行），产品id为26的产品，profit列为900，sale_year和sale_province列值都是NULL。

单字段分组

统计每年利润

SELECT `sale_year`, SUM( `profit` ) `profit` FROM `product_profit` GROUP BY `sale_year`;

MySQL中GROUP BY修饰符ROLLUP使用

既统计每年利润，也统计总利润

SELECT `sale_year`, SUM( `profit` ) `profit` FROM `product_profit` GROUP BY `sale_year` WITH ROLLUP;

MySQL中GROUP BY修饰符ROLLUP使用

在GROUP BY子句中添加WITH ROLLUP修饰符会使查询产生ROLLUP汇总行，这里该行显示总利润。

这里留下一个问题，如何区分常规分组行和ROLLUP汇总行？等会我们讲这个问题。

多字段分组

统计每年每个产品在每个省份销量的利润

SELECT `sale_year`, `product_id`, `sale_province`, SUM( `profit` ) `profit` FROM `product_profit` GROUP BY `sale_year`, `product_id`, `sale_province`;

MySQL中GROUP BY修饰符ROLLUP使用

增加ROLLUP汇总行

SELECT `sale_year`, `product_id`, `sale_province`, SUM( `profit` ) `profit` FROM `product_profit` GROUP BY `sale_year`, `product_id`, `sale_province` WITH ROLLUP;

MySQL中GROUP BY修饰符ROLLUP使用

看一下增加的汇总行

在给定年份(sale_year)和产品id(product_id)每个分组之后，都会出现一个销售地区(sale_province)的值为null，汇总每个产品每年(所有销售地区)总利润(profit的值)的汇总行。

在给定年份(sale_year)每个分组之后，都会出现一个产品id(product_id)和销售地区(sale_province)的值都为null，汇总每年总利润(profit的值)的汇总行。

最后一行，出现年份(sale_year)、产品id(product_id)和销售地区(sale_province)的值都为null，总利润(profit的值)的汇总行。

要注意的是：

ROLLUP汇总行中的值是在分组后放入结果集中的，因此ROLLUP汇总行只能在SELECT子句或者HAVING子句中使用，你不能在WHERE子句中使用，应该使用HAVING子句。

SELECT `sale_year`, `product_id`, `sale_province`, SUM( `profit` ) `profit`, GROUPING(`sale_year`) `rollup_year`,  GROUPING(`product_id`) `rollup_product_id`,  GROUPING(`sale_province`) `rollup_province`FROM `product_profit` GROUP BY `sale_year`, `product_id`, `sale_province` WITH ROLLUP HAVING `sale_province` IS NULL;

MySQL中GROUP BY修饰符ROLLUP使用

这里出现了利润为900的三行(红框中三行)，大家可能会困惑，这三行含义分别是分组常规行，ROLLUP地区汇总行，ROLLUP产品汇总行。至于rollup_year、rollup_product_id、rollup_province这三列，这个是区分分组常规行还是ROLLUP汇总行的，在下面会讲解，接着往下看都行了。

思考一下，修改GROUP BY子句列(`sale_year`, `product_id`, `sale_province`)的顺序后，profit列含义？

如果分组的列常规值是NULL值，ROLLUP汇总也是NULL，如何区分哪行是分组常规行，那一行是ROLLUP汇总行？

MySQL中GROUP BY修饰符ROLLUP使用

可以通过GROUPING()函数来测试NULL值来确定这行是否ROLLUP汇总行。GROUPING(expr [, expr] )返回1表示是ROLLUP汇总行。例如，GROUPING(`sale_province`)返回1表示是ROLLUP汇总行，0表示是常规分组行。

SELECT `sale_year`, `product_id`, `sale_province`, SUM( `profit` ) `profit`, GROUPING(`sale_year`) `rollup_year`,  GROUPING(`product_id`) `rollup_product_id`,  GROUPING(`sale_province`) `rollup_province`FROM `product_profit` GROUP BY `sale_year`, `product_id`, `sale_province` WITH ROLLUP HAVING `sale_province` IS NULL;

MySQL中GROUP BY修饰符ROLLUP使用

这里要注意：

GROUPING()只能在SELECT子句，HAVING子句和（自MySQL 8.0.12起）ORDER BY子句中使用该函数。

你也可以使用GROUPING()标签代替ROLLUP汇总行NULL值：

SELECTIF( GROUPING ( `sale_year` ), 'all_years', `sale_year` ) `sale_year`,IF( GROUPING ( `product_id` ), 'all_product', `product_id` ) `product_id`,IF( GROUPING ( `sale_province` ), 'all_province', `sale_province` ) `sale_province`, SUM( `profit` ) `profit` FROM `product_profit` GROUP BY `sale_year`, `product_id`, `sale_province` WITH ROLLUP;

MySQL中GROUP BY修饰符ROLLUP使用

GOUPING()注意事项

1、GOUPING()只能在SELECT子句，HAVING子句和（自MySQL 8.0.12起）ORDER BY子句中使用。

SELECTIF( GROUPING ( `sale_year` ), 'all_years', `sale_year` ) `sale_year`,IF( GROUPING ( `product_id` ), 'all_product', `product_id` ) `product_id`,IF( GROUPING ( `sale_province` ), 'all_province', `sale_province` ) `sale_province`, SUM( `profit` ) `profit` FROM `product_profit` GROUP BY `sale_year`, `product_id`, `sale_province` WITH ROLLUP ORDER BY GROUPING ( `sale_province` ) DESC;

2、LIMIT也会限制ROLLUP汇总行

SELECTIF( GROUPING ( `sale_year` ), 'all_years', `sale_year` ) `sale_year`,IF( GROUPING ( `product_id` ), 'all_product', `product_id` ) `product_id`,IF( GROUPING ( `sale_province` ), 'all_province', `sale_province` ) `sale_province`, SUM( `profit` ) `profit` FROM `product_profit` GROUP BY `sale_year`, `product_id`, `sale_province` WITH ROLLUP  LIMIT 5;