【MySQL】分组汇总实例(面试题入门到拓展)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了【MySQL】分组汇总实例(面试题入门到拓展),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2571字,纯文字阅读大概需要4分钟。
内容图文
![【MySQL】分组汇总实例(面试题入门到拓展)](/upload/InfoBanner/zyjiaocheng/525/9cf5050a211f472a9a394b79bcb687e6.jpg)
https://mp.weixin.qq.com/s/BBsN46t1KQ3tW13gLdBmcw
原表数据
目标表输出:
-- 创建表 create table student ( id varchar(20), name varchar(20), gender char(1), birth varchar(20), department varchar(20), address varchar(20) ) charset = utf8; -- 插入数据 insert into student values ("201901","张大佬","男","1985","计算机系","北京市海淀区"), ("201902","郭大侠","男","1986","中文系","北京市昌平区"), ("201903","张三","女","1990","中文系","湖南省永州市"), ("201904","李四","男","1990","英语系","辽宁市阜新市"), ("201905","王五","女","1991","英语系","福建省厦门市"), ("201906","王六","男","1988","计算机系","湖南省衡阳市");
第一步分组:
select department 院系, case gender when "男" then 1 else 0 end 男, case gender when "女" then 1 else 0 end 女 from student;
第二部汇总:外面套一层
select 院系, sum(男) 男, sum(女) 女, sum(男) + sum(女) as 总计 from ( select department 院系, case gender when "男" then 1 else 0 end 男, case gender when "女" then 1 else 0 end 女 from student ) a group by 院系;
原解题思路,详见上述公众号
-----------------------分割线------------------------------
以上思路比较清晰易懂,但是需要执行两次查询,如果数据量比较大,比如超过50W条记录时,查询效率不高。
以下为个人改进版
-- 分组汇总 select department 院系, sum(case gender when "男" then 1 else 0 end) as 男, sum(case gender when "女" then 1 else 0 end) as 女 from student group by department;
套用公式:
sum(case ...when...then...else...end) as xxx
其中sum,可以改成任意集合函数,如count,max,min等等
有人可能会说,decode也可以啊,decode只能用在oracle
-- Oracle decode写法 select department 院系, sum(decode(gender ,”男” ,1 , 0 )) as 男, sum(decode(gender ,”女” ,1 , 0)) as 女 from student group by department;
如果要在MySQL实现上面的方法,除了case when then else end,
还有if和decode相似,感谢群友(深圳-小小明)提供思路
select department 院系, sum(if(gender ="男" ,1, 0)) as 男, sum(if(gender ="女" ,1, 0)) as 女 from student group by department;
未聚合
select department 院系, if(gender ="男" ,1, 0) as 男, if(gender ="女" ,1, 0) as 女 from student;
---------------------------
再拓展下
加上合计:count(gender),或者sum(1),因为gender字段本身不具备数学运算
如果gender字段为空,合计可能要调整,具体还得看实际业务,不在本次考虑范围内。
学会了没,是不是很简单
【MySQL】分组汇总实例(面试题入门到拓展)
标签:数据分析 实现 class image name 思路 list 北京 目标
本文系统来源:https://www.cnblogs.com/hightech/p/13112263.html
内容总结
以上是互联网集市为您收集整理的【MySQL】分组汇总实例(面试题入门到拓展)全部内容,希望文章能够帮你解决【MySQL】分组汇总实例(面试题入门到拓展)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。