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

本题中使用两种方法：

1.使用where子句过滤条件 :先筛选再分组
2.使用分组group by ：分组的结果在进行筛选

1.使用where子句过滤条件

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

2.使用分组group by

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