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

select uid,exam_id,

round(if(max_score-min_score>0,(avg(score-min_score)/(max_score-min_score)*100),sum(score)),0) as avg_new_score
from
    (select exam_id,
	min(score) as min_score,
	max(score) as max_score
	from exam_record join examination_info
	using(exam_id) where difficulty='hard'
	and score is not null
	group by exam_id) as score_list 
join exam_record using(exam_id)
where score is not null
group by uid,exam_id
order by exam_id,avg_new_score desc