题解 | #SQL类别高难度试卷得分的截断平均值#
SQL类别高难度试卷得分的截断平均值
http://www.nowcoder.com/practice/a690f76a718242fd80757115d305be45
SELECT
t.tag,
t.difficulty,
round(
avg(t.score),1)
from (
select
e2.tag,
e2.difficulty,
e1.score,
dense_rank()over(order by e1.score desc) as m ,
dense_rank()over(order by e1.score asc) as d
from exam_record e1 left join examination_info e2 on e1.exam_id=e2.exam_id
WHERE
e2.tag="SQL"
and
e2.difficulty='hard'
and
e1.submit_time is not null
) as t
WHERE
t.m!=1 and t.d!=1
开窗写法，请大佬多多指点。
