100天SQL面试刷题 Day001

先看题目自己试着解一下，有困难的同学可以先看考点解析试下，
最后再看SQL解答，有不同意见和解法的欢迎评论区留言交流

题目：

来源：大厂(HW)面试真题

表T2包含了员工所在部门、工号、姓名，
表T1记录了各部门对一个产品的评价，可以多次打分，
筛选出每人评价的最高分的记录，并输出部门，姓名，评分，没有姓名的用工号代替。

表T1:

部门	工号	分数
A	1	70
A	2	70
A	2	90
A	2	80
A	1	80
B	1	6
B	1	7
B	1	8
C	1	80

表T2:

部门	工号	姓名
A	1	甲
A	2	乙
B	1	丙

题目下载

结果输出

考点解析

1. 分组聚合group by
2. ifnull

IFNULL(expression_1,expression_2);
--如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果

3. left join

• 先使用group by +max聚合出每个人的最高评分，需要使用部门和工号一起聚合，因为不同部门的工号是会重复的，部门+工号才是唯一的

select 部门,工号,max(分数)
FROM t1
group by 部门,工号`

• left join 以聚合后的表为基准表，匹配t2员工信息表中的员工姓名，

select * from (select 部门,工号,max(分数)
FROM t1
group by 部门,工号) tmp
left join t2
on tmp.部门 = t2.部门
and tmp.工号 = t2.工号

• 对姓名加一个ifnull判断， 没匹到的使用工号替代

select tmp.部门, ifnull(t2.姓名,tmp.工号) 员工, tmp.评分 from (select 部门,工号,max(分数) 评分
FROM t1
group by 部门,工号) tmp
left join t2
on tmp.部门 = t2.部门
and tmp.工号 = t2.工号

SQL代码

select t1.部门,
ifnull(姓名,t1.工号) 员工,
max(分数) FROM t1
left join t2
on t2.部门 = t1.部门 and t2.工号 = t1.工号
group by t1.部门,t1.工号

延伸思考

• 分析函数 sum max min 分组时，什么时候需要开窗 over（） 什么时候使用group by 直接聚合
  聚合结果需要跟每行去运算时候使用over可以保留原值，否则不需要用over
  eg：本题改为 去除最高和最低分的平均打分

• eg:本题改为 去除1个最高份和1个最低分的平均打分

• 【注】：开窗后的字段不能直接筛选 ，必须在外面嵌套一层查询（不能where 也不能having 区别于其他计算衍生字段（能having不能where））

注意事项

• MySQL报错1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated 解决方法