题解 | #检索所有列#

    uid,exam_id,
    round(avg(if(max_min_score=0,score,(score-min_score)/max_min_score*100)))avg_new_score#如果最大值与最小值之差为0，则不做归一化
FROM
    (SELECT#子查询
        uid,exam_id,score,
        (max(score)over(partition by exam_id)-min(score)over(partition by exam_id))max_min_score,#窗口函数直接求出归一函数的分母
        min(score)over(partition by exam_id)min_score#窗口函数求最小值
    FROM
        exam_record JOIN examination_info USING(exam_id)#两表联接
    WHERE
        difficulty='hard' AND score is not null)a#过滤条件：高难度、有分数
GROUP BY
    uid,exam_id
ORDER BY
    exam_id,avg_new_score DESC;