SQL简单查询业务表中完全重复的脏数据方法

分析完几种找重复数据的写法，我们先把结论摆出来： 最稳妥的方案，是用表里的所有字段做 GROUP BY，配合 HA VING COUNT(*) > 1 筛出重复。如果字段里含有大量 NULL，或者字段太多写起来太烦，那么用 ROW_NUMBER() 窗口函数来标记重复行会更聪明。还有一种场景，用 EXISTS 做自连接验证，适合小规模快速确认。不管选哪种，动手删数据之前，一定要先用 SELECT 把结果捞出来看一遍，确认无误再执行 DELETE。

好，咱们一个一个细说。 ### 用 GROUP BY + HA VING 找出完全重复的整行记录 要查出哪些行在表里出现了不止一次，最稳妥的做法就是把所有字段都丢进 GROUP BY。MySQL、PostgreSQL、SQL Server 都吃这套写法，但有个细节需要注意：当字段里出现 NULL 时，不同数据库的脾气不一样。比如 MySQL 默认认为两个 NULL 是相等的，会把它俩归到同一组；而 SQL Server 在 GROUP BY 里对 NULL 的处理也有些差异，具体得看版本和配置。 实际操作时可以记这么几条： 1. 先看看这个表的建表语句，有没有主键或唯一约束。出现完全重复，十有八九是建表时忘了加约束。 2. 避免在大表上直接 SELECT * 做分组，可以先跑一个 COUNT(*) 看看重复的频次分布，再用子查询去捞具体记录。 3. 如果字段数量多到写到手软，不妨用脚本从 INFORMATION_SCHEMA.COLUMNS 里把字段列表拼出来，省得手误。 ```sql SELECT *, COUNT(*) AS cnt FROM orders GROUP BY order_id, user_id, amount, created_at, status HA VING COUNT(*) > 1; ``` ### 用窗口函数 ROW_NUMBER() 标记并过滤重复行 相比之下，ROW_NUMBER() 窗口函数的灵活度更高。它能完整保留原始行的结构，不会像 GROUP BY 那样把结果压缩成一行。而且窗口函数对各数据库的 NULL 处理比较统一，少了一个踩坑点。 几点建议： * ORDER BY 子句里一定要放一个能让记录稳定排序的字段，比如自增 ID。否则同一组内 ROW_NUMBER() 的结果可能每次都不一样，那就彻底乱套了。 * 如果目标只是删重复、留一条，通常取 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY id) = 1；如果要删掉所有重复行，连一条都不留，那就用 COUNT(*) OVER (PARTITION BY ...) > 1 来判断。 * PostgreSQL 和 SQL Server 可以在 CTE 里直接用窗口函数。MySQL 8.0+ 也支持，老版本就得包一层子查询。 ```sql WITH dup AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY order_id, user_id, amount, created_at, status ORDER BY id ) AS rn FROM orders ) SELECT * FROM dup WHERE rn > 1; ``` ### WHERE 子句里用 EXISTS 或 IN 处理小规模去重验证 如果表不大，比如只有几万行，而且只是想确认某几列组合有没有重复，用 EXISTS 比全字段分组轻松得多。它不需要 GROUP BY 处理 NULL，逻辑更直白。 要注意的是： * EXISTS 的写法只适合“找任意一对重复”，不是“列出全部重复块”。想看清完整的重复组，还得回到前两种方法。 * 关联条件里别忘了加 `AND t1.id != t2.id`，否则每行都会跟自己匹配上，永远返回真。 * 如果字段里含 TEXT 或 JSON 这种没法直接比较的类型，大概率会报错。这种情况下得先做 CAST，或者干脆把这类列排除在判重范围之外。 ```sql SELECT DISTINCT t1.* FROM orders t1 WHERE EXISTS ( SELECT 1 FROM orders t2 WHERE t2.order_id = t1.order_id AND t2.user_id = t1.user_id AND t2.amount = t1.amount AND t2.created_at = t1.created_at AND t2.status = t1.status AND t2.id != t1.id ); ``` ### 删重复数据前务必加 WHERE 条件限制影响范围 线上跑 DELETE 是一件让人手心冒汗的事。哪怕逻辑看上去没问题，条件写宽了一点，就可能把正常数据一起删了。恢复成本远比你想象的高。 几点忠告： * 永远先用 SELECT 把要删的记录查出来，挑个三五条样本仔细看一眼。 * 生产环境千万不要写 `DELETE FROM table GROUP BY ...` —— 这根本就不是标准语法，容易写出错误的自连接删除。 * 如果用 DELETE + JOIN 或子查询，一定确认子查询结果没有歧义。比如 MySQL 不允许在子查询里直接引用待删的表，会报错。 * 数据量上千万时，分批删比单次大事务稳妥，省得把表锁太长时间。 说到底，真正麻烦的从来不是找重复数据那一步，而是确认哪些字段该参与判重、哪些 NULL 是业务允许的、以及删完后怎么把约束补上，防止问题再生。这些东西，不是写一条 SQL 就能解决的。