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

SELECT
    tag,
    difficulty,
    ROUND(avg(score),1) AS clip_avg_score
FROM
(SELECT 
    tag,
    difficulty,
    score,
    ROW_NUMBER() OVER(PARTITION BY tag,difficulty ORDER BY score ASC) AS rk1,
    ROW_NUMBER() OVER(PARTITION BY tag,difficulty ORDER BY score DESC) AS rk2
FROM exam_record
JOIN examination_info USING(exam_id)
WHERE tag = 'SQL' AND difficulty = 'hard' AND submit_time IS NOT NULL)t
WHERE rk1<>1 and rk2 <>1
GROUP BY tag,difficulty;