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

select
    t2.tag,
    t2.difficulty,
    round(avg(t1.score),1) clip_avg_score
from 
    exam_record t1
left join
    examination_info t2
on 
    t1.exam_id = t2.exam_id
where 
    t2.tag = 'SQL'
    and t1.score is not null
    and t1.score not in  (select  distinct min(score) from exam_record where exam_id in (select distinct exam_id from examination_info where tag = 'SQL') group by exam_id)
    and t1.score not in  (select distinct max(score) from exam_record where exam_id in (select distinct exam_id from examination_info where tag = 'SQL') group by exam_id)
group by
    t2.tag,
    t2.difficulty