搜文章
推荐 原创 视频 Java开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发
Lambda在线 > 码农闲聊 > MySQL中GROUP BY修饰符ROLLUP使用

MySQL中GROUP BY修饰符ROLLUP使用

码农闲聊 2020-05-24

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;


版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《MySQL中GROUP BY修饰符ROLLUP使用》的版权归原作者「码农闲聊」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

关注码农闲聊微信公众号

码农闲聊微信公众号:gh_b3d10cf23ae4

码农闲聊

手机扫描上方二维码即可关注码农闲聊微信公众号

码农闲聊最新文章

精品公众号随机推荐

下一篇 >>

MySQL基础(一)