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

#方法一
select
    score min_score_over_avg
FROM
    exam_record
join examination_info
on examination_info.exam_id=exam_record.exam_id
where
    score >= (
        select
            
            avg(score)
        from
            exam_record er
             left join examination_info ei on er.exam_id = ei.exam_id
        where
            tag = 'SQL'
        and er.score is not null
    )
and tag='SQL'
order by score
limit 1;

#方法二
select
    min(t.score) min_score_over_avg
FROM
    (
        select
            er.score,
            avg(er.score) over() avg_score
        
        from
            exam_record er
            left join examination_info ei on er.exam_id = ei.exam_id
        where
            ei.tag = 'SQL'
        and er.score is not null
    )t
where t.score >= t.avg_score;



#方法三
select
    min(t.score) min_score_over_avg
from (
    select 
        avg(er.score) over() avg_score,
        er.score
    from  exam_record er
    left join examination_info ei
    on er.exam_id = ei.exam_id
    where tag = 'SQL'
    and er.score is not null
)t
where  t.score>=t.avg_score;