题意: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