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

一开始想的是这么写，奈何一直报语法错误，不知道该怎么查出最大的row_number然后放在where条件中

SELECT 
tag, difficulty, AVG(score) clip_avg_score
FROM
(
	SELECT
		tag, difficulty, score,
		ROW_NUMBER() over (PARTITION BY tagORDER BY score) AS rn
	FROM examination_info info
	RIGHT JOIN exam_record record ON info.exam_id = record.exam_id
	WHERE tag = 'SQL' AND difficulty = 'hard' AND score IS NOT NULL
) t
WHERE rn > 1 AND rn < MAX(rn)
GROUP BY tag, difficulty

ChatGPT给出了解决方案

WITH HighDifficultySQL AS (
    SELECT
        e.tag,
        e.difficulty,
        r.score,
        ROW_NUMBER() OVER (PARTITION BY e.exam_id ORDER BY r.score) AS rn
    FROM examination_info e
    JOIN exam_record r ON e.exam_id = r.exam_id
    WHERE e.tag = 'SQL' AND e.difficulty = 'hard' AND r.score IS NOT NULL
)
SELECT tag, difficulty, AVG(score) AS clip_avg_score
FROM HighDifficultySQL
WHERE rn > 1 AND rn < (SELECT MAX(rn) FROM HighDifficultySQL)
GROUP BY tag, difficulty;