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

with m_score as(
    select r.exam_id, min(score) as mins, max(score) as maxs, count(*) as cnt from examination_info e right join exam_record r on e.exam_id = r.exam_id
    where difficulty = 'hard'
    group by exam_id
)
select uid, r.exam_id, 
round(100*avg(if(maxs != mins, (r.score - mins)/(maxs - mins), r.score/100)), 0)  as avg_new_score from exam_record r 
left join examination_info e on e.exam_id = r.exam_id 
left join m_score m on r.exam_id = m.exam_id
where difficulty = 'hard'
group by r.exam_id, uid
having avg_new_score is not null
order by exam_id asc, avg_new_score desc

不错的模拟题，注意除0问题，if(maxs != min)进行判断，我一开始还想着只有一个人作答的情况，这样子太狭隘了，多个人做到了同样的分数也会有这样的情况，if(maxs != min)更加全面