如何将表中所有数据复制到同表并使指定列连续编号

问题场景 #

现有test表结构及初始数据如下：

需要复制表中所有数据，且Test1列保持连续递增的编号，最终期望结果：

但执行简单INSERT后，Test1列会重复原编号（出现两个1、两个2），不符合需求。你尝试的SQL语句也未实现连续编号。

你的SQL错误分析 #

1. INSERT语句存在列名错误：SELECT test,test FROM test里的test是表名而非列名，即使修正为test1, test2，直接SELECT原test1值也会导致插入重复编号。
2. 后续更新逻辑错误：临时表用row_number() over (order by test1)生成的rn，在关联更新时用temp.tmp.test1 = test.test1，会让所有test1值相同的行被设置为同一个rn，无法实现连续唯一的编号。

正确解决方案 #

方案一：插入时直接生成连续编号（推荐） #

无需后续更新，插入时直接计算新的Test1值，一步到位：

INSERT INTO test (Test1, Test2)
SELECT 
    (SELECT MAX(Test1) FROM test) + ROW_NUMBER() OVER (ORDER BY Test1),
    Test2
FROM test;

原理：先获取当前test表中Test1的最大值，再给原表每一行按顺序分配递增的序号，两者相加得到新的连续编号。

方案二：若需先插入再更新（不推荐，效率较低） #

如果必须先插入重复编号的数据再修正，需要用行唯一标识来关联（不同数据库的行唯一标识语法不同，需按需调整），确保每一行对应唯一的序号：

-- 先插入数据（修正列名错误）
INSERT INTO test (Test1, Test2) SELECT Test1, Test2 FROM test;

-- 创建带唯一行标识和连续序号的临时表（PostgreSQL用ctid，MySQL可改用主键/自增列，Oracle用rowid）
CREATE TEMPORARY TABLE tmp AS
SELECT 
    ctid AS row_id,
    ROW_NUMBER() OVER (ORDER BY Test1, ctid) AS rn
FROM test;

-- 通过唯一行标识更新Test1
UPDATE test
SET Test1 = tmp.rn
FROM tmp
WHERE test.ctid = tmp.row_id;

-- 删除临时表
DROP TABLE tmp;

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