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

# 1.以exam_record为主表，联表，让tag,difficulty,score在一起
# 2.where筛选条件：tag = sql and difficuty = hard
# 3.计算阶段截断平均值：= (sum(score) - max(score) - min(score) ) / count(score) - 2, 取前两位小数点
# 注意点：题目求的是所有用户-->无需根据用户分组，虽然group by 经常和聚合函数一起用，但是对于整个表进行聚合的时候，就不需要用到group by

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