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

select
    uid,
    er.exam_id as exam_id,
    if(maxi=mini,round(avg(score)),round(avg(score-mini)/(maxi-mini)*100)) as avg_new_score
from (
	select
		exam_id,
		max(score) as maxi,
		min(score) as mini
	from exam_record
	where exam_id in (select exam_id from examination_info where difficulty='hard') and score is not null
	group by exam_id
)m ,exam_record er
where m.exam_id=er.exam_id and score is not null
group by uid,exam_id
order by exam_id,avg_new_score desc