评论区的同学指正了一个问题，题目中要求去掉一个最大值和一个最小值，我最初的理解就是直接去掉极值，但现在想想应该是在最大最小值重复的情况下只去掉一个。因此更新了一下答案。

select tag, difficulty, round(avg(score),1) as clip_avg_score
from
(select *, max(ranking) over (partition by tag, difficulty) as max_index, 
 min(ranking) over (partition by tag, difficulty) as min_index
from
(select tag, difficulty, score, row_number() over (order by score desc) as ranking
from examination_info inner join exam_record using(exam_id)
where tag = "SQL" and difficulty = "hard" and score is not null) as temp) as temp2
where ranking <> max_index and ranking <> min_index

原版答案如下

好像整太复杂了。首先是新建一个表temp1查询出最大最小值，然后连接两个原表并限制条件，并且限制score要小于最大值大于最小值。最后用avg找出平均分数并限制一位小数。

with temp1 as
(select er.exam_id, min(er.score) as mins, max(er.score) as maxs 
 from exam_record as er, examination_info as ei
 group by er.exam_id)
 
select ei.tag, ei.difficulty, round(avg(er.score),1)
from exam_record as er, examination_info as ei
where er.exam_id = ei.exam_id and ei.tag="SQL" and ei.difficulty = "hard"
and er.score > (select mins from temp1 where exam_id = er.exam_id)
and er.score < (select maxs from temp1 where exam_id = er.exam_id)

好家伙没想到这码分儿这么高