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

with a as(
select i.exam_id,max(r.score) as maxScore,min(r.score) as minScore 
from exam_record r,examination_info i 
where r.exam_id = i.exam_id and i.difficulty = 'hard' and r.score is not null
group by i.exam_id
)

select i.tag,i.difficulty,round(avg(r.score),1) from exam_record r,examination_info i 
where r.exam_id = i.exam_id and i.difficulty = 'hard' and r.score is not null
and not exists(select 1 from a where a.exam_id = i.exam_id and a.maxScore = r.score)
and not exists(select 1 from a where a.exam_id = i.exam_id and a.minScore = r.score)
group by i.tag,i.difficulty

先按科目ID分组取出最大最小的得分，然后再排除最大最小得分之后分组。