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

1.求值说明

各类比赛中，涉及到去掉最高分，去掉最低分后再取平均值的情况，以下案例给出答案，仅供参考。
本次案例用到row_number ( ) over ( PARTITION BY name ORDER BY score DESC )函数（点击方法直接到详解界面）

1.代码展示

SELECT
	t.name AS 演讲人,
	max( t.pjf ) AS 最高分,
	min( t.pjf ) AS 最低分,
	count( t.name ) + 2 AS 评分人数,
	round( avg( t.pjf ), 4 ) AS 平均得分,
	row_number ( ) over ( ORDER BY round( avg( t.pjf ), 4 ) DESC ) AS 'rank' 
FROM
	(
SELECT
	name AS name,
	score AS pjf,
	row_number ( ) over ( PARTITION BY name ORDER BY score DESC ) AS rank1,-- 倒序第一最大值
	row_number ( ) over ( PARTITION BY name ORDER BY score ASC ) AS rank2 -- 顺序第一最小值
FROM
	score_table
	) AS t 
WHERE
	t.rank1 <> 1 -- 去掉最高分
	AND t.rank2 <> 1 -- 去掉最低分
GROUP BY
	t.name 
ORDER BY
	平均得分 DESC

3.最终结果展示