题解 | #对试卷得分做min-max归一化#

select uid,
e.exam_id as exma_id,
if(max_score=min_score,score,round(avg((score-min_score)/(max_score-min_score))*100)) as avg_new_score
from exam_record e join 
(
select t1.exam_id,
max(t1.score) as max_score,
min(t1.score) as min_score
from exam_record t1 join examination_info t2
on t1.exam_id=t2.exam_id
where t2.difficulty='hard'
group by t1.exam_id)a
on e.exam_id=a.exam_id
where score is not null
group by e.uid,e.exam_id
order by e.exam_id,avg_new_score desc;