記一道群友的數(shù)據(jù)分析面試真題，你會(huì)嗎？
?近期在群里面看到了如下這樣一個(gè)面試題目，這個(gè)題目其實(shí)難度不大，但是你是否能夠很快寫出這個(gè)答案來呢？
本文解答方案由群友老黃提供
老黃博客：https://blog.csdn.net/weixin_41261833/article/details/105739549?utm_source=app
建表語句
create table student (id varchar(20),name varchar(20),gender char(1),birth varchar(20),department varchar(20),address varchar(20)) charset = utf8;
插入數(shù)據(jù)
insert into student values("201901","張大佬","男","1985","計(jì)算機(jī)系","北京市海淀區(qū)"),("201902","郭大俠","男","1986","中文系","北京市昌平區(qū)"),("201903","張三","女","1990","中文系","湖南省永州市"),("201904","李四","男","1990","英語系","遼寧市阜新市"),("201905","王五","女","1991","英語系","福建省廈門市"),("201906","王六","男","1988","計(jì)算機(jī)系","湖南省衡陽市");
結(jié)果如下
第一步
selectdepartment 院系,case gender when "男" then 1 else 0 end 男,case gender when "女" then 1 else 0 end 女from student;
結(jié)果如下
第二步
select院系,sum(男) 男,sum(女) 女,sum(男) + sum(女) as 總計(jì)from(select department 院系,case gender when "男" then 1 else 0 end 男,case gender when "女" then 1 else 0 end 女from student) agroup by 院系;
結(jié)果如下
怎么樣，學(xué)會(huì)了嗎？歡迎大家加群交流數(shù)分~
評(píng)論
圖片
表情