题解 | #SQL类别高难度试卷得分的截断平均值#

select
    tag,
    difficulty,
    round(avg(score), 1) as clip_avg_score
from
    (
        select
            b.tag,
            b.difficulty,
            a.score,
            max(a.score) over () as max_score,
            min(a.score) over () as min_score
        from
            exam_record a
            join examination_info b on a.exam_id = b.exam_id
            and b.tag = 'SQL'
            and b.difficulty = 'hard'
    ) t
where
    score < max_score
    and score > min_score
group by
    tag,
    difficulty