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

分析题目

题目条件：

SQL中高难度，所以筛选条件为 where difficulty ='hard' and tag='SQL'

难点1.

截断平均值 (sum(score)-max(score)-min(score))/(count(score)-2)

难点2.

保留一位小数 round（...,1）

难点3.

考虑连接

注：看了大佬的原来可以不用group by，本人一直以为用了聚合函数一定要用group by 实际这里的sum，count都是针对全表而言的，可以不用group by

初次提交版本：

from (select ex.exam_id ,tag ,difficulty,round((sum(score)-max(score)-min(score))/(count(score)-2),1) as clip_avg_score 
from examination_info  ex
inner join exam_record exr
on ex.exam_id=exr.exam_id
group by exr.exam_id ) e
where  difficulty ='hard' and tag='SQL'

更改后版本

from examination_info  ex
inner join exam_record exr
on ex.exam_id=exr.exam_id
where  difficulty ='hard' and tag='SQL'

这个占用内存更少，省去了多余的查询