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

select
	uid,
    exam_id,
	round(avg(ifnull(((score - min_score) / (max_score - min_score)) * 100, score)),0) avg_new_score
	
from exam_record
join examination_info
	using (exam_id)
join (
	select
		exam_id,
		case
			when count(*) = 1 then 0
			when count(*) > 1 then min(score)
			else null
		end as min_score,
		case
			when count(*) = 1 then 0
			when count(*) > 1 then max(score)
			else null
		end as max_score
	from exam_record
	join examination_info
		using (exam_id)
	where difficulty = 'hard' and submit_time is not null
	group by exam_id
) t
using (exam_id)
group by uid, exam_id
having avg_new_score is not null
order by exam_id, avg_new_score desc;