题解 | #计算商场折扣#

利用窗口排序函数row_number()over()筛选出去掉一个最大值和一个最小值后的SQL高难度试卷成绩表，再计算其平均值。

SELECT tag, difficulty, clip_avg_score
FROM (
    SELECT tag, difficulty, 
        ROUND(SUM(score) / COUNT(score), 1) AS clip_avg_score 
    FROM (
        SELECT tag, difficulty, score,
            ROW_NUMBER()OVER(ORDER BY score ASC) AS rank_asc,
            ROW_NUMBER()OVER(ORDER BY score DESC) AS rank_desc
        FROM examination_info
            JOIN exam_record
            USING(exam_id)
        WHERE tag = 'SQL' AND difficulty = 'hard' AND score IS NOT NULL
    ) order_table
    WHERE rank_asc > 1 AND rank_desc > 1
    GROUP BY tag, difficulty
) s_table