MySQL数据表垃圾数据清理：合并重复记录生成新表需求

我来帮你搞定这个数据清理的难题！先理清楚咱们的需求和数据情况，再一步步写出精准的SQL语句。

原表数据 #

先把你的原表用表格清晰展示：

清理规则回顾 #

• 合并同一学生的重复记录
• Registration字段取该学生所有记录中的最大年份（忽略空值）
• Course字段：若存在多个有效值（如Mary的两条完整记录），按Course升序取第一条；若只有一个有效值（如John仅有的CS）则直接取该值

解决方案SQL #

这里用窗口函数来实现需求，逻辑清晰且高效：

WITH processed_records AS (
    SELECT
        Student,
        -- 提取每个学生的最大注册年份，替换当前记录的Registration值
        MAX(Registration) OVER (PARTITION BY Student) AS Registration,
        Course,
        -- 给每个学生的记录排序：优先保留有Course的记录，再按Course升序编号
        ROW_NUMBER() OVER (
            PARTITION BY Student
            ORDER BY CASE WHEN Course IS NOT NULL THEN 0 ELSE 1 END, Course ASC
        ) AS rn
    FROM your_table -- 替换成你的实际表名
)
SELECT Student, Registration, Course
FROM processed_records
WHERE rn = 1 AND Course IS NOT NULL;

代码逻辑解释 #

1. CTE processed_records：
   • MAX(Registration) OVER (PARTITION BY Student)：为每个学生计算出最大的注册年份，不管当前记录的Registration是否为空，都统一替换成这个最大值，解决John的空值问题。
   • ROW_NUMBER()窗口函数：按学生分组，先把包含有效Course的记录排在前面（通过CASE语句给非空Course标记0，空标记1），再按Course升序排序，这样每个学生的第一条有效记录就是我们需要的。
2. 最终查询：筛选出每个学生的第一条有效记录（rn=1），并且确保Course不为空，得到目标结果。

执行结果 #

运行上述SQL后，会得到你期望的结果：

内容的提问来源于stack exchange，提问作者user1615666