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

select MIN(score) AS min_score_over_avg
FROM exam_record  AS er
JOIN examination_info AS ei using (exam_id)
WHERE ei.tag = 'SQL' AND
score >= (
    select AVG(score) FROM exam_record 
    WHERE exam_id in (
    select distinct exam_id FROM examination_info
    WHERE tag = 'SQL'
)
)

也可以用两个子查询，把子查询提取出来

WITH SQLExams AS (
    SELECT DISTINCT exam_id
    FROM examination_info
    WHERE tag = 'SQL'
)
SELECT MIN(score) AS min_score_over_avg
FROM exam_record
WHERE exam_id IN (SELECT exam_id FROM SQLExams)
  AND score >= (SELECT AVG(score) FROM exam_record WHERE exam_id IN (SELECT exam_id FROM SQLExams));