题解 | #SQL类别高难度试卷得分的截断平均值#
SQL类别高难度试卷得分的截断平均值
https://www.nowcoder.com/practice/a690f76a718242fd80757115d305be45
# 牛客的运营同学想要查看大家在SQL类别中高难度试卷的得分情况。
# 请你帮她从exam_record数据表中计算所有用户完成SQL类别高难度试卷得分的截断平均值
select t2.tag,t2.difficulty
,round((sum(t1.score)-max(t1.score)-min(t1.score))/(count(distinct t1.id)-2),1) as clip_avg_score
from exam_record t1
left join examination_info t2
on t1.exam_id = t2.exam_id
where t2.tag = 'SQL'
and t2.difficulty = 'hard'
and t1.score is not null
group by t2.tag,t2.difficulty
# 请你帮她从exam_record数据表中计算所有用户完成SQL类别高难度试卷得分的截断平均值
select t2.tag,t2.difficulty
,round((sum(t1.score)-max(t1.score)-min(t1.score))/(count(distinct t1.id)-2),1) as clip_avg_score
from exam_record t1
left join examination_info t2
on t1.exam_id = t2.exam_id
where t2.tag = 'SQL'
and t2.difficulty = 'hard'
and t1.score is not null
group by t2.tag,t2.difficulty
