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

/*  死方法
with tmp as (
select 
        a.exam_id,
        a.tag, 
        a.difficulty,
        score
from 
    (select 
        exam_id,
        tag, 
        difficulty
    from examination_info
    where tag='SQL' and difficulty='hard') a
    inner join exam_record b on a.exam_id=b.exam_id 
    where score is not null)

select 
    tag,
    difficulty,
    round(avg(score),1)
from tmp 
where  score > (select min(score) from tmp) and score < (select max(score) from tmp)
group by tag, difficulty
*/
 # 推荐使用这种方法，当然也可以row_number两次
select 
        a.tag, 
        a.difficulty,
        round((sum(score)-max(score)-min(score))/(count(score)-2), 1)
from 
    (select 
        exam_id,
        tag, 
        difficulty
    from examination_info
    where tag='SQL' and difficulty='hard') a
inner join exam_record b on a.exam_id=b.exam_id 
where score is not null
group by a.tag, a.difficulty