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

select
ei.tag,ei.difficulty,round(avg(t1.score),1)
from(
	select 
		uid,
		exam_id,
		score,
		row_number() over(partition by exam_id order by score) min_score,
		row_number() over(partition by exam_id order by score desc) max_score
	from exam_record
	where score is not null
)t1
join examination_info ei
on ei.exam_id = t1.exam_id 
WHERE ei.difficulty = 'hard' and ei.tag = 'SQL' and t1.min_score!=1 and t1.max_score !=1
group by ei.tag,ei.difficulty

先按照最大值和最小值排序打标签然后排除掉为1的值之后直接求平均值