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

# 先查一个小表
with t as (SELECT ei.tag, ei.difficulty, er.score FROM
examination_info ei, exam_record er
where ei.tag = 'SQL' and ei.difficulty = 'hard'
and ei.exam_id = er.exam_id and er.score is not null)

select tag, difficulty, round(avg(score),1)    # round(avg(score),1) 为保留一位小数
from t
# 排除掉最大和最小值
where score != (select max(score) from t)
and score != (select min(score) from t);

其中小表内容为

SQL|hard|80
SQL|hard|81
SQL|hard|84
SQL|hard|90
SQL|hard|50