原文:看似简单的一道SQL面试题,你是否能够很快写出答案?
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字段为空,合计可能要调整,具体还得看实际业务,不在本次考虑范围内。
学会了没,是不是很简单