题解 | #SQL类别高难度试卷得分的截断平均值#
SQL类别高难度试卷得分的截断平均值
http://www.nowcoder.com/practice/a690f76a718242fd80757115d305be45
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
复杂的开窗,求出最大,最小值,分数不等于最大,最小值就可得到要求平均成绩的数据。