如何修正PostgreSQL中生成连续行号的UPDATE语句以删除重复行

嘿，我来帮你解决这个问题！

首先来说说你原来的SQL为什么会失败：你的UPDATE语句里，子查询虽然生成了连续的行号，但没有把主表的每一行和子查询里对应的行关联起来。数据库执行的时候，主表的每一行都会匹配到子查询的所有行，最终只会取子查询的第一行结果，所以所有row_num都被设成了1。

第一步：生成正确的连续行号 #

如果你的数据库是PostgreSQL（从语法风格来看很像），可以利用系统字段ctid（它能唯一标识表中的每一行）来关联主表和子查询，修改后的SQL如下：

-- 先添加行号字段
ALTER TABLE test_table ADD COLUMN row_num INTEGER;

-- 用CTE生成带唯一标识的行号，再关联更新
WITH numbered_rows AS (
    SELECT ctid, ROW_NUMBER() OVER (ORDER BY Col1) AS rn
    FROM test_table
)
UPDATE test_table
SET row_num = numbered_rows.rn
FROM numbered_rows
WHERE test_table.ctid = numbered_rows.ctid;

执行后就能得到你想要的连续行号结果：

+------+---------+
| Col1 | row_num |
+------+---------+
| a1   | 1       |
| b1   | 2       |
| b1   | 3       |
| c1   | 4       |
| c1   | 5       |
| c1   | 6       |
+------+---------+

如果是其他数据库（比如MySQL 8.0+），如果表有主键（比如id），可以这样写：

ALTER TABLE test_table ADD COLUMN row_num INT;

UPDATE test_table t
JOIN (
    SELECT id, ROW_NUMBER() OVER (ORDER BY Col1) AS rn
    FROM test_table
) AS sub
ON t.id = sub.id
SET t.row_num = sub.rn;

第二步：更高效的删除重复行方法 #

其实你最终的目的是删除重复行，只保留每组的一行，完全不需要先添加行号字段再删除，有更直接的方式：

PostgreSQL版本：

WITH duplicate_rows AS (
    SELECT ctid, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) AS rn
    FROM test_table
)
DELETE FROM test_table
USING duplicate_rows
WHERE test_table.ctid = duplicate_rows.ctid AND duplicate_rows.rn > 1;

这个语句会给每个Col1分组的行编号，只保留编号为1的行，删除其他重复行。

MySQL 8.0+版本（假设有主键id）：

WITH duplicate_rows AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY id) AS rn
    FROM test_table
)
DELETE FROM test_table
WHERE id IN (SELECT id FROM duplicate_rows WHERE rn > 1);

如果是没有窗口函数的旧版MySQL，可以用自连接删除：

DELETE t1 FROM test_table t1
JOIN test_table t2 
ON t1.Col1 = t2.Col1 AND t1.id > t2.id;

这个语句会保留每个Col1分组中id最小的那一行，删除其他重复行。

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