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

select tag,difficulty,round(avg(score),1) as clip_avg_score
from(
select tag,difficulty,score,ec.exam_id exam_id
from exam_record ec
join examination_info ei
on ec.exam_id = ei.exam_id
where difficulty = 'hard'
and score != '(NULL)') as t2
join
(select ec.exam_id exam_id,max(score) as max,min(score) as min
from exam_record ec
join examination_info ei
on ec.exam_id = ei.exam_id
where difficulty = 'hard'
and score != '(NULL)'
group by ec.exam_id) as t1
on t1.exam_id = t2.exam_id
where score != max and score != min
group by t2.exam_id,tag,difficulty

神经病写法