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

with a as     
    (select ei.tag, ei.difficulty,er.score
    from exam_record er 
    inner join examination_info ei on ei.exam_id =er.exam_id 
    where ei.tag='SQL'
        and er.score is not null and ei.difficulty='hard')

select tag,difficulty,round(avg(score),1) clip_avg_score from a 
where a.score<> (select max(score) from a) 
and a.score<> (select min(score) from a)
group by 1,2