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

一： SELECT tag,difficulty, ROUND( (SUM(score)-MAX(score)-MIN(score))/(COUNT(score)-2) ,1) clip_avg_score FROM examination_info ei RIGHT JOIN exam_record er ON ei.exam_id=er.exam_id WHERE tag='SQL' AND difficulty='hard'

二： SELECT tag,difficulty,ROUND(AVG(score),1) clip_avg_score FROM (SELECT tag,difficulty,score,rn FROM examination_info e RIGHT JOIN (SELECT *, ROW_NUMBER () OVER (PARTITION BY exam_id ORDER BY score DESC) RN FROM exam_record WHERE score IS NOT NULL) a ON e.exam_id = a.exam_id WHERE tag='SQL' AND difficulty='hard') b WHERE rn NOT IN (1,(SELECT MAX(rn) FROM (SELECT tag,difficulty,score,rn FROM examination_info e RIGHT JOIN (SELECT *, ROW_NUMBER () OVER (PARTITION BY exam_id ORDER BY score DESC) RN FROM exam_record WHERE score IS NOT NULL) a ON e.exam_id = a.exam_id WHERE tag='SQL' AND difficulty='hard' ) c));