题解 | #得分不小于平均分的最低分的简洁实现#

select min(if(score >= avg_score, score, null)) as min_score_over_avg 
from 
(
    select score, avg(score) over (partition by tag) as avg_score
    from 
    exam_record as er inner join 
    examination_info as ei 
    on er.exam_id = ei.exam_id 
    and ei.tag = 'SQL'
) t

第一步: 利用窗口函数, 即以非聚合的方式获得sql类别(事实上可以是所有类别)的平均分字段avg_score

第二步: min函数内部筛选, if(score >= avg_score, score, null)

好处:

1. 一层子查询
2. 无需重复连接表
3. 容易扩展至多类别的情形