题解 | #得分不小于平均分的最低分#
得分不小于平均分的最低分
https://www.nowcoder.com/practice/3de23f1204694e74b7deef08922805b2
方法一：
SELECT MIN(score) AS min_score_over_avg
FROM exam_record a LEFT JOIN examination_info b ON a.exam_id = b.exam_id
WHERE tag = 'SQL'
AND score IS NOT NULL
AND score >=
(SELECT AVG(score) AS avg
FROM exam_record a LEFT JOIN examination_info b ON a.exam_id = b.exam_id
WHERE tag = 'SQL' AND score IS NOT NULL);
方法一是先查询出来符合要求的平均分，然后利用子查询，用WHERE比较SQL试卷里分数大于等于平均分的记录，再SELECT后面使用MIN(score)就筛选出符合题目要求的分数了。
SELECT MIN(t.score) AS min_score_over_avg
FROM
(SELECT uid, tag, score, AVG(score) OVER(PARTITION BY tag) AS avg
FROM exam_record a LEFT JOIN examination_info b ON a.exam_id = b.exam_id
WHERE score IS NOT NULL) t
WHERE t.tag = 'SQL' AND t.score >= t.avg;
方法二是使用窗口函数，用AVG() OVER()，按照tag标签分区，在联结的表后面增加一列平均分，把这个表当成新的表（去掉分数空值的记录），然后利用子查询FROM刚才的新表，再用WHERE条件筛出来SQL试卷和score大于等于avg(score)的记录，最后对这些记录求一个最小分数就可以。
