题解 | #SQL16 得分不小于平均分的最低分#
得分不小于平均分的最低分
http://www.nowcoder.com/practice/3de23f1204694e74b7deef08922805b2
明确题意:
找到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'
)
SQL进阶 文章被收录于专栏
SQL进阶step by step