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

反正我是想不出特别好的实现了……

with tmp as (select ei.tag,ei.difficulty,score,row_number() over(order by score asc)as rn 
from exam_record er 
join examination_info ei 
on ei.tag='SQL' and ei.difficulty='hard' and er.exam_id=ei.exam_id 
where er.submit_time is not null) 
select tag,difficulty,round(avg(score),1)as clip_avg_score from tmp 
join (select max(rn) as mrn from tmp)c 
on tmp.rn<>c.mrn 
where rn<>1 
group by tag,difficulty;