去掉最高分最低分求平均分

业务场景：期末考试，5名数学老师对全校考生的数学科目打分，考生的最终分数为5名老师打分去掉一个最高，一个最低然后取平均分，保留两位 小数。
*

---

直接拿文档最后的语句即可，中间内容记录的是本人的思考过程。

---

分析：
（有评分记录表，t_pfjd包含bmh（考生密号）、pfyh（评分老师用户）、pf(评分)）
1、每一位考生对应有5个打分，去掉最高分和最低分 。通过sql实现的话，肯定要实现分组排序。
首先想到了 group by bmh(考生密号) order by pf（评分）
select bmh,pf from t_pfjd order by bmh,pf

同一位考生的成绩排列到了一起，下一步就是处理去掉最高最低
很明显无法直接去掉,因为要对每一个考生的打分记录里做排序，去掉第一个和第五个。搬出函数：OVER(PARTITION BY… ORDER BY…)
函数实现目的为 按照指定的字段进行分割成，然后分隔成的组内按照某个字段排序
select bmh,pf,ROW_NUMBER() OVER(PARTITION BY bmh ORDER BY pf) rn from t_pfjd
可以看到跟 使用order by 的效果类似，但是可以增加了一个组内排序的序号 rn。

说明下三个函数
select bmh,pf,
–ROW_NUMBER() --先查出的排名在前,没有重复值
–rank()–是跳跃排序,相同数据(这里为sal列相同)排名相同,比如并列第1,则两行数据(这里为rank列)都标为1,下一位将是第3名.中间的2被直接跳过了
dense_rank()–这个是连续排序的,比如两条并列第1,则两行数据(这里为rank列)都标为1,下一个排名将是第2名
OVER(PARTITION BY bmh ORDER BY pf) rn from t_pfjd
我们只去掉一个最高和一个最低，序号不能够重复，所以使用函数ROW_NUMBER() OVER(PARTITION BY… ORDER BY…)

2、排序以及组内排序ok了，下一步要考虑下如何去掉最高最低了。
可以直接 用 rn<> ‘1’ and rn <>‘5’
select * from (select bmh,pf,ROW_NUMBER() OVER(PARTITION BY bmh ORDER BY pf) rn from t_pfjd ) a where a.rn<> ‘1’ and a.rn <>‘5’
ok，已经去除了最高分和最低分。下一步要直接用avg()函数取平均分就可以了
select a.bmh,avg(a.pf) from (select bmh,pf,ROW_NUMBER() OVER(PARTITION BY bmh ORDER BY pf) rn from t_pfjd ) a where a.rn<> ‘1’ and a.rn <>‘5’ group by a.bmh
测试，可以实现。

3、如果评分老师有10个，要去掉两个最高分，两个最低分呢？用以上的语句就不是太工整了，我的目标是最终将语句做成系统功能，通过调整传入参数，计算考生的分数。调整下语句
select a.bmh,avg(a.pf) from (select bmh,pf,ROW_NUMBER() OVER(PARTITION BY bmh ORDER BY pf asc) rn1,–顺序排序
ROW_NUMBER() OVER(PARTITION BY bmh ORDER BY pf desc) rn2–倒序排序
from t_pfjd ) a
where a.rn1>1 --去掉一个最低分，去几个传入数字几
and a.rn2>1----去掉一个最高分
group by a.bmh