select
t.tag,
t.difficulty,
round(
avg(t.score),1)
from (
select
e1.tag,
e1.difficulty,
e2.score,
max(e2.score)over(partition by e2.exam_id  ) as m,
min(e2.score)over(partition by e2.exam_id  ) as d
from  examination_info e1 left join exam_record e2 on e1.exam_id=e2.exam_id
where
e1.tag='SQL' and e1.difficulty='hard'
) as t 
WHERE
t.score!=t.d and t.score!=t.m

复杂的开窗，求出最大，最小值，分数不等于最大，最小值就可得到要求平均成绩的数据。