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

select tag,difficulty,round(avg(score),1) as cilp_avg_score
from (
    select tag,difficulty,score,
    rank() over (order by score )as rank_min,
    rank() over (order by score desc) as rank_max
    from examination_info a,exam_record b
     where a.exam_id = b.exam_id 
     and a.tag = "SQL"
     and a.difficulty = "hard"
     and score is not null) as t1
where rank_min !=1 and rank_max != 1
group by tag;