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

SELECT a.tag,a.difficulty,ROUND(AVG(a.score),1) clip_avg_score
FROM (
    SELECT i.tag,i.difficulty,r.score,ROW_NUMBER() over (PARTITION by i.tag ORDER BY r.score ) rn1,
    ROW_NUMBER() over (PARTITION by i.tag ORDER BY r.score  DESC) rn2
    FROM exam_record r
    LEFT JOIN examination_info  i ON r.exam_id=i.exam_id
    WHERE r.score is NOT NULL 
    ) as a 
    WHERE a.rn1<>1 and a.rn2<>1 AND a.tag='SQL' AND a.difficulty='hard'
    GROUP BY a.tag,a.difficulty

利用ROW_NUMBER() 窗口函数给成绩排名，分别升序排名和降序排名，均去掉第一名再求平均值