逻辑很简单，就是先用聚合加窗口函数算出归一值，然后再取平均值。

select uid, exam_id, cast(avg(standard) as unsigned) as avg_new_score from

(select uid, exam_id, 
if(count(score) over (partition by exam_id) = 1, score, 
   100*(score - min(score) over (partition by exam_id))/ (max(score) over (partition by exam_id) - min(score) over (partition by exam_id))) as standard
from examination_info inner join exam_record using(exam_id)
where difficulty = "hard" and score is not null) as temp

group by uid, exam_id
order by exam_id, avg_new_score desc