题意:SQL类试卷中,得分大于等于SQL类试卷平均得分的最小得分,即tag = SQL,score >= avg(score),score = min(score)。
分析:
SQL类试卷:
select exam_id from examination_info where tag = 'SQL'SQL类试卷平均得分:
select avg(score) from exam_record where exam_id in (select exam_id from examination_info where tag = 'SQL')方法一:IN子查询。
select score min_score_over_avg from exam_record where exam_id in (select exam_id from examination_info where tag = 'SQL') and score >= (select avg(score) from exam_record where exam_id in (select exam_id from examination_info where tag = 'SQL')) order by score limit 1方法二:联表+IN子查询。
select e1.score min_score_over_avg from exam_record e1 left join examination_info e2 on e1.exam_id = e2.exam_id where e2.tag = 'SQL' and e1.score >= (select avg(score) from exam_record where exam_id in (select exam_id from examination_info where tag = 'SQL')) order by min_score_over_avg limit 1方法三:case表达式。
select case when exam_id in (select exam_id from examination_info where tag = 'SQL') and score >= (select avg(score) from exam_record where exam_id in (select exam_id from examination_info where tag = 'SQL')) then score else '空' end as min_score_over_avg from exam_record order by min_score_over_avg limit 1方法四:谓词exists。
select score min_score_over_avg from exam_record e1 where exists (select * from examination_info e2 where e2.tag = 'SQL' and e2.exam_id = e1.exam_id) and score >= (select avg(score) from exam_record where exam_id in (select exam_id from examination_info where tag = 'SQL')) order by score limit 1