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

思路：

去掉 正序+倒序排序 top1的行即完成截断的筛选

步骤：

1）exam_record表用is not null筛选出已完成的信息

2）examination_info表筛选题目要求信息（tag='SQL' and difficulty = 'hard'）

3）上述两个子查询以exam_id为连接键join一下

4）where限制一下正序和倒序都不等于1（去掉score字段最大值和最小值）

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