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

## 使用row_number()窗口函数获取排名（不重复，不跳过，保证只减去第一个最大值和最小值）
SELECT 
    tag,
    difficulty,
    ROUND(AVG(score),1) AS clip_avg_score
FROM(
    SELECT 
    er.exam_id,
    score,
    tag,
    difficulty,
    ROW_NUMBER() OVER (PARTITION BY tag,difficulty 
    ORDER BY score DESC) AS high_rank,
    ROW_NUMBER() OVER (PARTITION BY tag,difficulty 
    ORDER BY score) AS low_rank
    FROM exam_record er
    JOIN examination_info ei USING(exam_id)
    WHERE tag = 'SQL' AND difficulty = 'hard' AND 
    score IS NOT NULL 
) AS t1
WHERE high_rank <> 1 and low_rank <> 1
GROUP BY tag,difficulty