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

方法一

使用sum()函数求出所有的总和，再分别减去最大值max，最小值min，使用count()函数统计总个数，再减去一个最大值，一个最小值，所以减去2

使用round(X,D)函数保留小数位。X：为小数的值，D：为要保留的个数

SELECT tag, difficulty, round((sum(score)- max(score)- min(score))/(count(score)- 2),1) as 'clip_avg_score'
FROM
    (
    SELECT
        tag,
        difficulty,
        score 
    FROM
        exam_record
        INNER JOIN examination_info USING ( exam_id ) 
    WHERE
        tag = 'SQL' 
        AND difficulty = 'hard' AND score IS NOT NULL 
    ) AS temp

方法二

使用窗口函数分别对分数进行升序排列，降序排列，起别名为 asc 和 desc，将查询的结果作为一张临时表使用，表的别名为 temp

再将升序排列序号为1的数据，和降序排列序号为1的数据，过滤掉，将剩余的数据进行去平均值计算，使用 avg() 函数

select 
    tag,
    difficulty,
    round(avg(score),1) as 'clip_avg_score' 
from(
    select
        tag,
        difficulty,
        score,
        rank() over (order by score) as 'asc',
        rank() over (order by score desc) as 'desc'
    from
        exam_record
        inner join examination_info USING(exam_id)
    where
        tag = 'SQL'
        and difficulty = 'hard'
        and score is not null) temp 
where temp.asc != 1 and temp.desc != 1 GROUP BY tag,difficulty