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

select
    tag,
    difficulty,
    round(avg(score), 1) clip_avg_score
from
    test.examination_info ei
    join test.exam_record er on ei.exam_id = er.exam_id
where
    tag = 'SQL'
    and difficulty = 'hard'
    and score not in (
        select
            min(score) score
        from
            test.examination_info ei
            join test.exam_record er on ei.exam_id = er.exam_id
        where
            tag = 'SQL'
            and difficulty = 'hard'
        union
        select
            max(score) score
        from
            test.examination_info ei
            join test.exam_record er on ei.exam_id = er.exam_id
        where
            tag = 'SQL'
            and difficulty = 'hard'
    )