GROUP BY + HAVING COUNT() > 1 是最直接的业务重复键检测方式;需用完整业务唯一键显式分组,COUNT() 不可替换为 COUNT(col),NULL 默认正确归组但须确认业务语义,查完整重复行应改用窗口函数。

GROUP BY + HAVING COUNT(*) > 1 是最直接的业务重复键检测方式
只要明确哪些列组合构成你的“业务唯一键”,就用它们做 GROUP BY,再加 HAVING COUNT(*) > 1 过滤。比如订单系统中“用户ID+商品ID+下单时间”不允许重复,那就写:
SELECT user_id, product_id, order_time, COUNT(*) AS cnt FROM orders GROUP BY user_id, product_id, order_time HAVING COUNT(*) > 1;
注意三点:
-
COUNT(*)必须用,不能写COUNT(col)—— 后者会跳过NULL值,导致含空值的重复组被漏掉 - 所有参与业务逻辑判断的列都得显式列在
GROUP BY中,不能偷懒写GROUP BY *(MySQL 8.0+ 虽支持但不可靠,PostgreSQL 不认) -
HAVING是必须的,WHERE在分组前执行,无法访问COUNT(*)结果
NULL 值不会破坏 GROUP BY 的分组一致性,但要注意语义是否合理
GROUP BY 默认把所有 NULL 当作同一组,这恰好符合“多个 NULL 值视为相等”的业务直觉。比如 email IS NULL 的多条记录会被归进同一组,COUNT(*) 也能正确统计。
但问题常出在业务定义上:你真想把“邮箱为空”的用户也当作潜在重复吗?
- 先查空值占比:
SELECT COUNT(*) FROM table WHERE col IS NULL - 如果空值太多且无业务意义,建议加
WHERE col IS NOT NULL预过滤 - 若需保留空值参与比对,别用
COALESCE(col, '')—— 字符串长度、排序规则(collation)可能引发隐式转换偏差
要拿到重复行的完整原始记录,就得用窗口函数
GROUP BY 只返回分组摘要,没法看到每条重复记录的 id、created_at 等字段。这时候必须切到窗口函数方案:
WITH dup_rows AS (
SELECT *,
COUNT(*) OVER (PARTITION BY user_id, product_id, order_time) AS dup_cnt
FROM orders
)
SELECT * FROM dup_rows WHERE dup_cnt > 1;
关键细节:
-
PARTITION BY列表必须和你定义的业务键完全一致,少一个就漏判 - 如果后续要删重,推荐加
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY id),方便取rn = 1作为保留行 - SQL Server / PostgreSQL / MySQL 8.0+ 支持;SQLite 不支持,得用自连接模拟,性能差很多
大表上跑聚合容易慢,索引和预筛选比优化 SQL 更有效
千万级表上直接 GROUP BY 三列,没索引时可能几小时不出结果。与其调写法,不如先看数据分布:
- 给业务键列建复合索引:
CREATE INDEX idx_user_prod_time ON orders(user_id, product_id, order_time) - 用
EXISTS快速探路是否存在重复:SELECT TOP 1 user_id FROM orders o1 WHERE EXISTS (SELECT 1 FROM orders o2 WHERE o2.user_id = o1.user_id AND o2.product_id = o1.product_id AND o2.order_time = o1.order_time AND o2.id != o1.id) - 如果只关心“有没有”,别用
COUNT(*);如果要导出全部重复行,优先走窗口函数 + CTE,避免多次扫描基表
真正容易被忽略的是业务键的完整性——比如漏掉了“是否测试单”这个标记字段,导致生产单和测试单被混在一起判重。查之前,先和产品/业务方确认:哪些字段共同构成“不可重复”的最小语义单元。











