SQL如何快速找出重复数据并编号 ROW_NUMBER去重技巧

SQL如何快速找出重复数据并编号 ROW_NUMBER去重技巧

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

用 ROW_NUMBER() 给重复行打序号，再筛出重复组

开门见山，先说一个核心结论：ROW_NUMBER() 这个窗口函数本身并不具备“去重”的魔法，它的核心任务仅仅是编号。真正的去重操作，需要你配合子查询或者公共表表达式（CTE），把那些编号为1（或者大于1）的行精准地筛选出来。整个过程的关键，首先在于明确定义“究竟依据哪些字段来判断重复”，然后通过 PARTITION BY 子句来分组编号。

一个新手常踩的坑，是写成 ROW_NUMBER() OVER (ORDER BY col) 这种形式。这会导致在全表范围内进行连续编号，根本没有进行分组，自然也就无法识别出哪些记录是彼此的“复制品”。

• 定义分组是关键：必须使用 PARTITION BY col1, col2 来指定判定重复的字段组合，比如单一的 email 字段，或者复合的 user_id, order_date。
• 组内排序求稳定：括号内的 ORDER BY 只负责控制每个分组内部的排序逻辑。通常选用 id 或 created_at 这类具有唯一性或时间顺序的字段，以确保每次执行的结果都稳定一致。
• 编号逻辑要看清：编号从1开始，且在每个分组内独立计数。因此，对于重复的数据组，必然会出现 rn = 1（通常被视作保留行）和 rn > 1（通常被视作待处理行）的记录。

查出所有重复记录（含编号），方便人工核对

这一步至关重要，却最容易被跳过：还没看清楚重复数据到底长什么样，就急着动手删除，很容易导致误删。更稳妥的做法是，先用CTE把编号结果封装起来，同时结合 COUNT(*) OVER (PARTITION BY ...) 窗口函数，直接计算出每组的记录条数。这样，数据状况便一目了然。

WITH duped AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn,
         COUNT(*) OVER (PARTITION BY email) AS cnt
  FROM users
)
SELECT * FROM duped WHERE cnt > 1;

这里需要特别注意两个筛选条件的区别：cnt > 1 用于筛选出“所有属于重复组”的行；而 rn = 1 则用于在重复组中“保留第一条”。在编写删除逻辑时，可千万别把这两个条件混淆了。

保留每组第一条，删掉其余重复行（安全删除写法）

直接执行 DELETE 语句风险较高，务必先进行数据备份或在事务中测试。此处的核心难点在于，如何让数据库精准定位“哪些行该被删除”。通常的做法是通过子查询关联原表的主键，而不是试图直接用 ROW_NUMBER() 的生成列进行删除（部分数据库不支持这种语法）。

• 语法因数据库而异：PostgreSQL / SQL Server / Oracle 等数据库支持 DELETE ... USING 或 DELETE FROM ... WHERE id IN (SELECT ...) 这类写法。
• MySQL的版本差异：MySQL 8.0+ 版本可以优雅地使用 CTE 配合 DELETE，但老版本可能需要借助临时表或自连接来实现。
• 一种更通用的替代方案：

  DELETE FROM users WHERE id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY email
  );
  

  这种使用 GROUP BY 和 MIN() 聚合函数的方法比 ROW_NUMBER() 更简洁，但它有一个局限：你无法灵活控制“保留哪一条”，只能依赖 MIN() 或 MAX() 这类聚合规则。

ROW_NUMBER() 和 DENSE_RANK() 在去重场景的区别

是不是有人尝试过用 DENSE_RANK() 来代替 ROW_NUMBER() 进行去重，却发现编号结果“不对劲”？原因在于，去重逻辑依赖于“组内是否能为每行生成唯一的序号”，而 DENSE_RANK() 函数会对相同的排序值赋予相同的排名，这导致它无法有效区分同一分组内的不同行。

• ROW_NUMBER()：在组内生成严格的 1, 2, 3… 序列。这让我们可以精准定位“第一条”和“非第一条”记录，是去重场景的理想选择。
• RANK() 或 DENSE_RANK()：当遇到相同的 ORDER BY 值时，会产生并列排名（例如 1, 1, 3 或 1, 1, 2）。这种情况下，你无法简单地用 rn = 1 来安全地保留唯一的一行。
• 从性能角度看，这几个函数差异不大，但语义清晰度天差地别。所以，别为了省事而随意替换函数。

话说回来，真正棘手的往往不是SQL怎么写，而是“重复”这个定义本身是否合理。举个例子，忽略空格、大小写或时区偏移的邮箱地址，到底算不算重复？这些问题必须在 PARTITION BY 之前就处理好，比如使用 TRIM(LOWER(email)) 进行标准化。否则，后续的编号分组从一开始就错了，再精巧的语法也是徒劳。