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

WITH ExamInfo AS (
    SELECT exam_id,tag,difficulty
    FROM examination_info
    WHERE tag = 'SQL' AND difficulty = 'hard'
)

SELECT
    ei.tag,
    ei.difficulty,
    ROUND(avg(er.score), 1) AS clip_avg_score
FROM
    ExamInfo AS ei
JOIN
    exam_record er ON ei.exam_id = er.exam_id
WHERE
    er.score NOT IN (
        (SELECT MAX(score) FROM exam_record WHERE exam_id = ei.exam_id),
        (SELECT MIN(score) FROM exam_record WHERE exam_id = ei.exam_id)
    )
GROUP BY
    ei.tag, ei.difficulty;

解法一：用withExamInfo AS (...)创建一个临时的子查询，该子查询返回符合条件的考试信息的eaxm_id,tag,difficulty

解法二 ：来自题解

select tag, difficulty,
ROUND((SUM(er.score) - MAX(er.score) - MIN(er.score)) / (COUNT(er.score) - 2), 1) AS clip_avg_score
FROM examination_info AS ei
JOIN exam_record AS er using(exam_id)
WHERE ei.tag = 'SQL' AND ei.difficulty = 'hard'

using 是对有相同的列两个表进行合并