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

-- 截断平均值，去除最大值和最小值，再算平均数
-- 1、先关联，内连接 2再过滤，sql和hard,过滤最大值，最小值 3、再算平均分 4、保留一位小数
SELECT 
    tag, difficulty, ROUND(AVG(score), 1)
FROM
    examination_info a
        INNER JOIN
    exam_record b ON a.exam_id = b.exam_id
WHERE
    tag = 'SQL' AND difficulty = 'hard'
        AND score IS NOT NULL
        AND score NOT IN ((SELECT 
            MAX(b.score)
        FROM
            examination_info a
                INNER JOIN
            exam_record b ON a.exam_id = b.exam_id
        WHERE
            tag = 'SQL' AND difficulty = 'hard'
                AND score IS NOT NULL) , (SELECT 
                MIN(b.score)
            FROM
                examination_info a
                    INNER JOIN
                exam_record b ON a.exam_id = b.exam_id
            WHERE
                tag = 'SQL' AND difficulty = 'hard'
                    AND score IS NOT NULL));