明确题意:
找到SQL试卷得分不小于该类试卷平均得分的用户最低得分
问题分解:
- 找到SQL类试卷得分的平均分。
- 试卷作答记录表关联试卷信息表:join using
- 筛选试卷类别:where tag='SQL'
- 取平均分:avg(score)
- 找到分数不小于平均分的记录:where tag='SQL' and score>=(...)
- 取最低分:min(score)
细节问题:
- 表头重命名:as
完整代码:
select min(score) as min_score_over_avg
from exam_record
join examination_info using(exam_id)
where tag='SQL'
and score >= (
select avg(score)
from exam_record
join examination_info using(exam_id)
where tag='SQL'
)