在SQL中如何用ROW_NUMBER实现无主键表的数据去重-SQL-PHP中文网

ROW_NUMBER()需配合PARTITION BY才能去重，核心是按指定字段分组并排序后取每组序号为1的行；直接使用仅生成序号，不删除重复。

在sql中如何用row_number实现无主键表的数据去重

ROW_NUMBER() 必须配合 PARTITION BY 才能去重

直接用 ROW_NUMBER() 不会自动去重，它只生成序号；真正控制“留哪一行”的是 PARTITION BY + ORDER BY 的组合。核心逻辑是：对需要去重的字段分组（PARTITION BY col1, col2...），再按某个规则排序（比如时间、ID、优先级），最后取每组中序号为 1 的行。

常见错误是只写 ORDER BY 而漏掉 PARTITION BY，结果整张表被编成 1, 2, 3…，毫无分组意义。

必须明确指定去重依据列，例如 PARTITION BY name, email 表示“姓名和邮箱都相同的算重复”
ORDER BY 决定保留哪条：想留最新数据就用 created_at DESC，想留最小 ID 就用 id ASC
无主键不等于无标识字段——哪怕只有 text 和 timestamp，也能作为 PARTITION BY 和 ORDER BY 的依据

DELETE 时不能直接在原表上用 ROW_NUMBER()

多数数据库（如 PostgreSQL、SQL Server）不允许在 DELETE 语句里直接嵌套窗口函数；MySQL 8.0+ 虽支持，但语法限制多，容易报错 ERROR 1288: The target table ... of the DELETE is not updatable。

安全做法是用 CTE 或子查询先算出要保留的行（比如 rn = 1），再通过主键或唯一组合关联删除。

推荐用 CTE：

WITH dedup AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY a, b ORDER BY ts DESC) AS rn
  FROM my_table
)
DELETE FROM my_table
WHERE (a, b, ts) IN (
  SELECT a, b, ts FROM dedup WHERE rn > 1
);

如果连联合唯一性都没有，得靠 ctid（PostgreSQL）或 %%physloc%%（SQL Server）等物理标识临时锚定行，否则可能误删
MySQL 用户注意：8.0 前不支持 CTE，只能用 JOIN 模拟，且需确保 JOIN 条件能精确匹配到待删行

没有主键时，ORDER BY 的选择直接影响结果稳定性

当多行完全相同时（所有字段值一致），ORDER BY 若无法打破并列，数据库可能任意选一行标为 rn = 1，导致每次执行结果不一致——这不是 bug，是标准行为。

解决办法不是避免并列，而是主动引入确定性排序依据：

加 ORDER BY ... , ctid（PostgreSQL）或 ORDER BY ... , %%physloc%%（SQL Server）保证物理顺序稳定
MySQL 可用 ORDER BY ... , (SELECT NULL) 不起作用，必须找一个隐式唯一字段，比如自增伪列（若存在）
如果真的一点可排序字段都没有，建议先用 ALTER TABLE ADD COLUMN _rowid SERIAL（PostgreSQL）打个补丁，再用于 ORDER BY

性能差往往是因为没加索引

PARTITION BY 字段组合就是天然的查询条件，但数据库不会自动为其建索引。全表扫描 + 窗口计算，在万级以上数据量时会明显变慢，甚至 OOM。

务必为 PARTITION BY 列创建复合索引，例如：CREATE INDEX idx_dup_cols ON my_table (col1, col2)
如果 ORDER BY 是 ts DESC，索引应写成 (col1, col2, ts DESC)，让排序也走索引
大表操作前先 ANALYZE my_table（PostgreSQL）或更新统计信息，避免优化器误判

实际执行前，先用 SELECT * 把 CTE 结果查出来看三行，确认 rn = 1 的确实是你要留下的那条——无主键场景下，少一次验证，多一分风险。