本文主要是介绍MySQL ——group by子句使用with rollup,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
group by 子句使用with rollup关键字之后,具有分组加和的功能。即:在所有的分组记录之后,自动新增一条记录,从全局计算所有记录的数据。
0 问题描述
求出每年的学生平均成绩,及历史至今的平均成绩,结果保留两位小数。
1 数据准备
create table rollup_test
(name varchar(8) COMMENT'',year int COMMENT'',score int COMMENT''
)CHARACTER SET utf8 COLLATE utf8_general_ci;insert into rollup_test
values ('a',2016, 85),('b',2016, 45),('c',2016, 90),('a',2015, 75),('b',2015, 90);
2 数据分析
完成代码如下:
select `year`,round(avg(score) ,2) as avg_score
from rollup_test group by `year`
union all
select '历史至今' as `year`,round(avg(score) ,2) as avg_score
from rollup_test
方式二:利用group by with rollup 替换union all进行简化
select `year`,round(avg(score) ,2) as avg_score
from rollup_test
group by `year`
with rollup
由输出结果可知,with rollup 会自动新增一条记录:
year avg_score
null 77.00
结果与下列代码等价
select '历史至今' as `year`,round(avg(score) ,2) as avg_score
from rollup_test
再借助函数ifnull 或 coalesce() 对空字段赋值
上述代码继续优化为:
select coalesce (`year`,'历史至今') as `year`,round(avg(score) ,2) as avg_score
from rollup_test
group by `year`
with rollup
输出结果为:
3 小结
这篇关于MySQL ——group by子句使用with rollup的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!