问题分解：

• 统计每次高难度试卷被完成的分数以及该卷的最高最低分，生成子表 t_exam_record_min_max:
• 关联试卷作答表和试卷信息表:exam_record JOIN examination_info USING(exam_id)
• 筛选做完了高难度的记录：WHERE score IS NOT NULL AND difficulty="hard"
• 拼接上该试卷的最低分：MIN(score) over(PARTITION BY exam_id) as min_score
• 拼接上该试卷的最高分：MAX(score) over(PARTITION BY exam_id) as max_score
• 按用户ID和试卷ID分组：GROUP BY uid, exam_id
• 计算归一化后分数，需分情况讨论：
• 如果最高分和最低分相等，即只有一个分值：直接返回原分值
• 否则归一化后取整：ROUND(AVG((score - min_score) / (max_score - min_score) * 100))

细节问题：

• 表头重命名：as
• 按试卷ID升序、归一化分数降序排序：ORDER BY exam_id, avg_new_score DESC

完整代码：

``````SELECT uid, exam_id,
IF(max_score > min_score,
ROUND(AVG((score - min_score) / (max_score - min_score) * 100)),
score
) as avg_new_score
FROM (
SELECT uid, exam_id, score,
MIN(score) over(PARTITION BY exam_id) as min_score,
MAX(score) over(PARTITION BY exam_id) as max_score
FROM exam_record JOIN examination_info USING(exam_id)
WHERE score IS NOT NULL AND difficulty="hard"
) as t_exam_record_min_max -- 每次高难度试卷被完成的分数以及该卷的最大最小分
GROUP BY uid, exam_id
ORDER BY exam_id, avg_new_score DESC;
``````