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

select exam_record.uid
,exam_record.exam_id as exam_id
,ifnull(round(avg(100*(score-mins)/(maxs-mins))),maxs) as avg_new_score
from exam_record 
left join examination_info using(exam_id)
left join 
(select exam_id,max(score) as maxs,min(score) as mins
from 
(select exam_record.exam_id as exam_id,score,uid
from exam_record left join examination_info using(exam_id)
where score is not null
and difficulty="hard") as t0
group by exam_id) as t1 using(exam_id)
where exam_record.score is not null
and examination_info.difficulty="hard"
group by exam_record.exam_id,exam_record.uid
order by exam_record.exam_id,avg_new_score desc