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

SELECT
    tag,
    difficulty,
    ROUND(AVG(score),1) as clip_avg_score
FROM(
    SELECT
        tag,
        difficulty,
        score,
        max_score
    FROM(
        SELECT
            tag,
            difficulty,
            score,
            MIN(score) OVER() as min_score,
            MAX(score) OVER() as max_score
        FROM(
            SELECT
                e.difficulty,
                e.tag,
                er.score
            FROM examination_info e
            INNER JOIN exam_record er
            ON e.exam_id = er.exam_id
                AND (er.score IS NOT NULL AND e.tag = 'SQL')
                    AND e.difficulty = 'hard'
        )t
    )t1
    WHERE score != min_score
)t2
WHERE score != max_score
GROUP BY tag,difficulty

写题前一定要把各个字段看清楚，不然搞半天发现字段写错了。

1 内层循环先进行表的连接，然后过滤score为NULL、tag为'SQL'、difficulty为'hard'的所有记录；

2 再往外一层循环找到当前记录的最大值和最小值

3 外面的两层循环，一次是找到当前score不等于最小值的所有记录；一次循环是找到当前score不等于最大值的所有记录；

SELECT
    tag,
    difficulty,
    ROUND(AVG(score), 1) as clip_avg_score
FROM(
    SELECT
        e.tag,
        e.difficulty,
        er.score,
        RANK() OVER(order by score DESC) as max_rk,
        RANK() OVER(order by score ASC) as min_rk
    FROM examination_info e
    INNER JOIN exam_record er
    ON e.exam_id = er.exam_id
        AND (er.score IS NOT NULL AND e.tag = 'SQL')
            AND e.difficulty = 'hard'
)t
WHERE max_rk <> 1 AND min_rk <> 1
GROUP BY tag, difficulty

这个方法中，连接方法和上面相同，不同的是在连接的时候使用RANK() 窗口函数对score进行正反向的排序

外层循环使用 <> 非等号来进行排序名次的筛选，进而可以过滤掉最大值和最小值.

最后使用 group by 进行分组，统计score的平均值即可.