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

# 查询用户完成SQL类别高难度试卷的所有得分情况，并制造不重复排序列
select score, row_number()over(order by score) as rank_score
from exam_record er
join examination_info ei
on ei.exam_id=er.exam_id
where tag = 'SQL' and difficulty = 'hard'
and score is not null  
# 查询这些分数的截断平均值
select 'SQL' as tag, 'hard' as difficulty, round(avg(score), 1) as clip_avg_score
from (
    select score, row_number()over(order by score) as rank_score
    from exam_record er
    join examination_info ei
    on ei.exam_id=er.exam_id
    where tag = 'SQL' and difficulty = 'hard'
    and score is not null  
) k1
where rank_score <> 1
and rank_score < (
    select count(score)
    from exam_record er
    join examination_info ei
    on ei.exam_id=er.exam_id
    where tag = 'SQL' and difficulty = 'hard'
    and score is not null  
);

笑死了，被自己搞这么复杂，其实聚合函数可以直接相加减然后作为一个字段值啊。。。

select (sum(score)-max(score)-min(score))/(count(score)-2)

然后就是聚合函数和表中普通字段一起查询时，要么是有group by 且普通字段在group by 后面；要么就要求给表设置筛选条件使得表中这多个普通字段都只有一个值。