一:
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));