题解 | #SQL类别高难度试卷得分的截断平均值#

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