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

-- 方法一 窗口函数
-- 1、拼表，并利用窗口函数对exam_id进行分区，并计算每个分区的平均得分
# select
#     score,
#     avg(score) over(partition by er.exam_id) avg_score
# from exam_record er left join examination_info ei on er.exam_id=ei.exam_id
# where tag='SQL'

-- 2、利用子查询，where过滤score大于均值的数据，并通过min取最小值
# select min(score) min_score_over_avg
# from (
#     select
#         score,
#         avg(score) over(partition by er.exam_id) avg_score
#     from exam_record er left join examination_info ei on er.exam_id=ei.exam_id
#     where tag='SQL'
# ) tmp
# where score>=avg_score

-- 方法二 子查询
-- 1、计算SQL试卷的平均得分
# select avg(score)
# from exam_record er left join examination_info ei on er.exam_id=ei.exam_id
# where tag='SQL'

-- 2、计算大于等于平均得分的用户最低分(此处不想重新用join,故用了两个子查询)
select min(score) min_score_over_avg
from exam_record
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 exam_id in (select exam_id from examination_info where tag='SQL')