明确题意：

将用户作答高难度试卷的得分在每份试卷作答记录内执行min-max归一化后缩放到[0,100]区间，并输出用户ID、试卷ID、归一化后分数平均值；

最后按照试卷ID升序、归一化分数降序输出。

问题分解：

• 统计每次高难度试卷被完成的分数以及该卷的最高最低分，生成子表 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;