大数据面试SQL042-所有考试科目的成绩都大于对应学科的平均成绩的学生(2)-CSDN博客

本文链接：https://blog.csdn.net/jarckv/article/details/138925254

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

| 2 | 3 | 22 |
| 3 | 1 | 30 |
| 3 | 2 | 50 |
| 3 | 3 | 52 |
| 4 | 1 | 90 |
| 4 | 2 | 90 |
| 4 | 3 | 72 |
±-----±-----±-------+


## 二、分析


题目要求找出每科科目成绩都大于对应学科平均成绩的学生，难点有两个：


1）给每行记录（每个学生每个学科）添加该学科的平均成绩，这里开窗函数可以解决；


2）查询出“所有”科目都大于平均成绩的学生，这里的所有比较难处理，有个技巧：对每个学生的每个科目满足“成绩>科目平均成绩”的记录打0，不满足的打1，然后对学生所有科目标签求和，和为0则满足“所有科目都大于平均成绩”，和>0则不满足；




| 维度 | 评分 |
| --- | --- |
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |


## 三、SQL


1）使用开窗函数给每行记录添加对应科目的平均成绩

select
sid,
cid,
score,
avg(score) over(partition by cid) as avg_score
from t_scores_042


查询结果  
 ![请添加图片描述](https://img-blog.csdnimg.cn/direct/ca96e83a4c9340f9ae5f43a359624dc6.jpeg)


2)给每个学生每个科目进行打标，科目成绩>科目平均成绩的标记为0，反之为1；

select
sid,
cid,
score,
avg_score,
if(score>avg_score,0,1) as flag
from
(
select
sid,
cid,
score,
avg(score) over(partition by cid) as avg_score
from t_scores_042
) t


查询结果  
 ![请添加图片描述](https://img-blog.csdnimg.cn/direct/7879bfe7f83944ac89608aa22b22d542.jpeg)


3）使用sum统计flag和为0的学生即满足条件的学生

select
sid
from
(
select
sid,
cid,
score,
avg_score,
if(score>avg_score,0,1) as flag
from
(
select
sid,
cid,
score,
avg(score) over(partition by cid) as avg_score
from t_scores_042
) t
) tt
group by sid
having sum(flag) = 0