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

## 灵活使用窗户函数，计算最高分数和最低分数
SELECT 
    tag,
    difficulty,
    ROUND(AVG(score),1) AS clip_avg_scoreclip_avg_score
FROM (
    SELECT 
    er.exam_id,
    score,
    tag,
    difficulty,
    MAX(score) OVER (PARTITION BY tag,difficulty 
    ORDER BY difficulty) AS max_score,
    min(score) OVER (PARTITION BY tag,difficulty 
    ORDER BY difficulty) AS min_score
    FROM exam_record er
    JOIN examination_info ei USING(exam_id) 
) AS t1
WHERE score > min_score AND score <  max_score
GROUP BY tag,difficulty
HAVING tag = 'SQL' AND difficulty = 'hard'