开窗函数解法

看了别的大佬的题解才发现搞复杂了

• 思路：考虑到多个分数重复的情况，利用row_number() over()窗口函数对exam_id分区，对score分别进行顺序排列（t_rank1）和倒序排列（t_rank2）。
• 因为窗口函数不像max()和min()会自动忽略null值，所以要在where子句把null值（即没做完的卷子分数）去掉。否则row_number也会对它们排序。
• 最后选定t_rank1 != 1 和t_rank2 != 1即可去掉最大值和最小值。

select t.tag, t.difficulty, round(avg(t.score),1) as clip_avg_score
from (select ei.exam_id, ei.tag, ei.difficulty, er.score
      ,ROW_NUMBER() over(PARTITION by er.exam_id order by er.score) as t_rank1
      ,ROW_NUMBER() over(PARTITION by er.exam_id order by er.score desc) as t_rank2
      from examination_info ei join exam_record er on ei.exam_id = er.exam_id 
      and ei.difficulty = 'hard'
      and ei.tag = 'SQL'
      where er.score is not null
     )t
where t.t_rank1 != 1
and t.t_rank2 != 1