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

# CTE法
# WITH zongbiao AS (
#     SELECT ei.exam_id, ei.tag, ei.difficulty, er.score
#     FROM examination_info AS ei
#     INNER JOIN exam_record AS er
#     ON ei.exam_id = er.exam_id
#     WHERE ei.tag = 'SQL' AND ei.difficulty = 'hard'
# ),
# min_max_score AS(
#     SELECT z.tag, MAX(z.score) AS max_score, MIN(z.score) AS min_score
#     FROM zongbiao AS z
#     GROUP BY z.tag
# )
# SELECT z.tag, z.difficulty, ROUND(AVG(z.score), 1) AS clip_avg_score
# FROM zongbiao AS z
# INNER JOIN  min_max_score AS mm
# ON z.tag = mm.tag 
# AND z.score > mm.min_score
# AND z.score < mm.max_score
# GROUP BY z.tag, z.difficulty;

# 一步到位使用子查询法
SELECT ei.tag, ei.difficulty, ROUND(AVG(er.score), 1) AS clip_avg_score
FROM examination_info AS ei
INNER JOIN exam_record AS er
ON ei.exam_id = er.exam_id
WHERE ei.tag = 'SQL' AND ei.difficulty = 'hard'
AND er.score != (SELECT MAX(er.score) FROM exam_record AS er
                 WHERE ei.exam_id IN (SELECT exam_id FROM examination_info WHERE difficulty  = 'hard' AND tag = 'SQL') )
AND er.score != (SELECT MIN(er.score) FROM exam_record AS er
                 WHERE ei.exam_id IN (SELECT exam_id FROM examination_info WHERE difficulty  = 'hard' AND tag = 'SQL') );