题解 | #得分不小于平均分的最低分#

一、明确需求

1. 题目要求：找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。
2. 需求解读：需要限制试卷类别为"SQL"，计算平均得分
3. 观察数据：exam_record表只有exam_id没有“sql”类别，需要联结examination_info匹配tag进行限制。

二、解题思路

1. exam_record联结examination_info表，限制tag="SQL"作为源表t1
2. 对t1根据tag为一组计算avg(score)
3. 对2的结果作为where子查询的筛选条件，对整个结果取min(score)

三、代码实现

#筛选数据
with 
t1 as 
(
select a.*,b.tag
from exam_record a inner join examination_info b using(exam_id)
where b.tag = 'SQL' 
),

# 计算平均值
t2 as 
(
select avg(score) avg_score
from t1
group by tag
)

select min(score) min_score_over_avg
from t1 
where score >= (select avg_score from t2)