如何将SQL表所有行复制5次并同步递增Portfolio_num与Sim_num列值？

需求 #

现有test表，初始数据如下：

需要把所有行复制5次，每次复制时Portfolio_num和Sim_num同步加1（最终得到0到5共6组数据），SECID和FV保持不变。

原表的创建和初始化语句：

CREATE TABLE test (
    Portfolio_num int,
    Sim_num int,
    SECID varchar(10),
    FV int
);

INSERT INTO test (Portfolio_num, Sim_num, SECID, FV)
VALUES
(0,0, 'TB20230421', 34000),
(0,0, 'TB20230521', 38000),
(0,0, 'TB20230621', 40000);

实现方案 #

方案1：递归CTE（支持CTE的数据库：MySQL 8+、PostgreSQL、SQL Server等） #

递归生成0到5的数字序列，和原表做笛卡尔积计算新的列值：

WITH RECURSIVE nums(n) AS (
    SELECT 0
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 5
)
SELECT 
    t.Portfolio_num + n AS Portfolio_num,
    t.Sim_num + n AS Sim_num,
    t.SECID,
    t.FV
FROM test t
CROSS JOIN nums
ORDER BY Portfolio_num, SECID;

如果要把新增数据插入原表（原表已有0组数据，所以从1开始生成序列）：

WITH RECURSIVE nums(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 5
)
INSERT INTO test (Portfolio_num, Sim_num, SECID, FV)
SELECT 
    t.Portfolio_num + n,
    t.Sim_num + n,
    t.SECID,
    t.FV
FROM test t
CROSS JOIN nums;

方案2：手动生成数字序列（兼容低版本数据库：MySQL 5.x等） #

直接用UNION ALL构造需要的数字序列，再和原表关联：

SELECT 
    t.Portfolio_num + nums.n AS Portfolio_num,
    t.Sim_num + nums.n AS Sim_num,
    t.SECID,
    t.FV
FROM test t
CROSS JOIN (
    SELECT 0 AS n UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5
) nums
ORDER BY Portfolio_num, SECID;

插入版本：

INSERT INTO test (Portfolio_num, Sim_num, SECID, FV)
SELECT 
    t.Portfolio_num + nums.n,
    t.Sim_num + nums.n,
    t.SECID,
    t.FV
FROM test t
CROSS JOIN (
    SELECT 1 AS n UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5
) nums;

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