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

首先，将科目表与用户信息分数表连接起来，明确好必要条件 接着，即可求取对应的得分值

select a.tag,a.difficulty, round(avg(a.scoren),1) as clip_avg_s
from
(
select r.uid,e.exam_id,e.tag,e.difficulty,e.duration,r.score as scoren
from examination_info e 
join exam_record r
on e.exam_id = r.exam_id 
where e.difficulty = 'hard'
and e.exam_id = '9001'
and r.score is not NULL
and r.score not in (select min(score)as min_s from exam_record r2 where r2.exam_id = '9001')
and r.score not in (select max(score)as max_s from exam_record r3 where r3.exam_id = '9001') 
)a
group by 2