原创 猴子 猴子数据分析 5月5日
来自专辑
图解SQL面试题
【题目】“学生表”里记录了学生的学号、入学时间等信息。“成绩表”里是学生选课成绩的信息。两个表中的学号一一对应。（滴滴2020年面试题）
现在需要：
1. 筛选出2017年入学的“计算机”专业年龄最小的3位同学名单（姓名、年龄）
2. 统计每个班同学各科成绩平均分大于80分的人数和人数占比
【解题思路】
问题1：筛选出2017年入学的“计算机”专业年龄最小的3位同学名单（姓名、年龄）
一看是不是有点懵？
别着急，我们用逻辑树分析方法，把这个复杂问题拆解为一个一个可以解决的简单问题：
1）筛选条件：入学时间是2017，专业是计算机
2）最小的3位同学名单（姓名、年龄）
1.先找出符合要求的同学
筛选条件：入学时间是2017，专业是计算机。year(日期)函数用来获取日期的年份
select 姓名,年龄from 学生表where 专业='计算机' and year(入学时间)=2017;2.最小的3位同学名单（姓名、年龄）
先使用order by对年龄排序（从小到大，也就是升序asc），然后使用limit输出前3行数据，就是年龄最小的3位。
select 姓名,年龄from 学生表where 专业='计算机' and year(入学时间)=2017order by 年龄 asclimit 3;问题2：统计每个班同学各科成绩平均分大于80分的人数和人数占比
一看是不是有点懵？
别着急，我们用逻辑树分析方法，把这个复杂问题拆解为一个一个可以解决的简单问题：
（1）每位同学的平均成绩
（2）平均分大于80分的人数
（3）平均分大于80分的人数占比
（4）输出结果是班级，平均分大于80分的人数，平均分大于80分的人数占比
1. 每位同学的平均成绩
涉及到“每个”的时候，就要想到《猴子 从零学会sql》里的分组汇总了。按学号分组（group by），然后求平均成绩（avg函数），把所得结果看做临时表。
select 学号,avg(分数) as 平均成绩from 成绩表group by 学号;2.平均成绩>80的人数
可以使用使用sum函数和case表达式来统计平均成绩大于80的人数
select sum(case when 平均成绩>80 then 1 else 0 end) as 人数from 临时表；下图是case和sum结合起来统计人数的sql过程：
3.平均成绩大于80分的人数占比
平均成绩>80的人数占比 =（平均成绩>80的人数）/ 总人数
总人数是表行数：count(学号)。所以平均成绩>80的人数占比就是：
select sum(case when 平均成绩>80 then 1 else 0 end)/count(学号) as 人数占比from 临时表；4. 输出结果是班级、人数、人数占比
班级在“学生表”中，这涉及到需要将“学生表”和“临时表”2张表，需要用到多表联结。联结两表的是“学号”，如下：
因为要保留“学生表”班级的全部数据，所以使用左联结。
select a.班级from 学生表 as a left join 临时表 as bon a.学号=b.学号group by 班级;题目要求是输出班级、人数、人数占比，所以在上面sql中加入输出的列名：
select a.班级,人数,人数占比from 学生表 as a left join 临时表 as bon a.学号=b.学号group by 班级;select子句中的人数、人数占比在前面第1步、第2步中已经得到，套入这个sql语句中就是：
最终sql如下：
select a.班级,sum(case when b.平均成绩>80 then 1else 0 end) as 人数,sum(case when b.平均成绩>80 then 1else 0 end)/count(a.学号) as 人数占比from 学生表 as a left join(select 学号,avg(分数) as 平均成绩from 成绩表group by 学号) as bon a.学号=b.学号group by 班级
【本题考点】
1.使用逻辑树分析方法将复杂问题变成简单问题的能力
2.当遇到“每个”问题的时候，要想到用分组汇总
3.查询最小n个数据的问题：先排序（order by），然后使用limit取出前n行数据
4.遇到有筛选条件的统计数量问题时，使用case表达式筛选出符合条件的行为1，否则为0。然后用汇总函数（sum）对case表达式输出列求和。
有筛选条件的统计数量问题的万能模板
select sum(case when <判断表达式> then 1 else 0end) as 数量from 信息表；【举一反三】
1.查询最小/最大的N个数据的问题
某网站有购买记录表，找出消费最大的2名顾客，输出顾客ID和消费金额
select 顾客ID,消费金额from 购买记录表order by 消费金额 desclimit 2;2. 分组汇总问题
某网站有顾客表和消费表，请统计每个城市的顾客平均消费在1000元以上的人数，输出城市，人数
select a.城市,sum(case when b.平均消费>1000 then 1else 0 end) as 人数from 顾客表 as a left join(select ID,avg(消费金额) as 平均消费from 消费表group by ID) as bon a.ID=b.IDgroup by 城市;