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

select tag,difficulty,round(avg(score),1) as clip_avg_score
from(
select tag,difficulty,score,
        row_number()over(order by score) as rn1,
        row_number()over(order by score desc) as rn2
from(
select 
t1.tag,t1.difficulty,t2.score
from 
(select exam_id,tag,difficulty from examination_info 
 where tag = 'SQL' and difficulty = 'hard')t1
join 
(select exam_id,score from exam_record where score is not null) t2
on t1.exam_id = t2.exam_id
    ) t
    )t3
where rn1 > 1 and rn2 > 1
group by tag,difficulty