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

#方法一 select tag, difficulty, round(avg(score),1) clip_avg_score from(select exam_id, tag, difficulty from examination_info where tag='SQL' and difficulty = 'hard')t1 join (select uid,exam_id,score from exam_record )t2 on t1.exam_id = t2.exam_id where t2.score>(select min(score) from exam_record where exam_id=t1.exam_id) and t2.score <(select max(score) from exam_record where exam_id = t1.exam_id)

#方法二：窗口函数 select tag, difficulty, round(avg(score),1) clip_avg_score FROM(SELECT E1.exam_id, tag, difficulty, score, row_number() over(partition by exam_id order by score) rk1, row_number() over(partition by exam_id order by score desc) rk2 from examination_info E1 join exam_record E2 on E1.exam_id = E2.exam_id where tag = 'SQL' and difficulty = 'hard' and score is not null)t1 where t1.rk1 > 1 and t1.rk2 > 1 group by tag,difficulty

#方法三 select tag, difficulty, round((sum(score) - max(score) - min(score)) / (count(score) - 2), 1) as clip_avg_score from exam_record join examination_info using(exam_id) where tag="SQL" and difficulty="hard" and score is not null