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

with
    sql_score as (
        select
            tag,
            difficulty,
            score,
            row_number() over (
                order by
                    score
            ) ranking
        from
            examination_info info
            join exam_record record on (info.exam_id = record.exam_id)
        where
            tag = 'SQL'
            and difficulty = 'hard'
            and score is not null
    )
select
    tag,
    difficulty,
    round(avg(score), 1) clip_avg_score
from
    sql_score
where
    ranking > 1
    and ranking < (
        select
            max(ranking)
        from
            sql_score
    )
group by
    tag,
    difficulty;

#法1：使用max和min先找出最大值和最小值，然后用union连起来，再用where not in 筛选出不是最大值和最小值的成绩

with t1 as (select

    tag,

    difficulty,

    score

from

    examination_info info

    join exam_record record on (info.exam_id = record.exam_id)

where

    tag = 'SQL'

    and difficulty = 'hard'

    and score is not null),

max_nin_score as (

select max(score) from t1

union

select min(score) from t1

)

select  tag,difficulty,round(avg(score), 1) clip_avg_score

from t1

where score not in (select * from max_nin_score)

group by tag,difficulty