SQL Server中基于SourceTable生成编码序列并插入单表的需求

你需要把SourceTable里的每个HeadNo扩展为10000条连续编码（格式为HeadNo+四位数字，从0000到9999），再合并插入到新表对吧？下面是针对不同主流数据库的实现方案，你可以按需选用：

核心思路 #

本质是先生成一个包含0到9999的数字序列，再和SourceTable做笛卡尔积，拼接成目标编码后插入新表（假设目标表名为TargetTable，结构为HeadNo VARCHAR(2)、ActualCode VARCHAR(6)）。

1. MySQL 8.0+（支持递归CTE） #

递归CTE可以直接生成数字序列，无需提前创建辅助表：

WITH RECURSIVE nums AS (
    SELECT 0 AS num
    UNION ALL
    SELECT num + 1 FROM nums WHERE num < 9999
)
INSERT INTO TargetTable (HeadNo, ActualCode)
SELECT 
    s.HeadNo,
    CONCAT(s.HeadNo, LPAD(n.num, 4, '0')) AS ActualCode
FROM SourceTable s
CROSS JOIN nums n;

2. SQL Server #

同样用递归CTE，但需要指定递归深度（默认上限是100，这里要设为10000）：

WITH nums AS (
    SELECT 0 AS num
    UNION ALL
    SELECT num + 1 FROM nums WHERE num < 9999
)
INSERT INTO TargetTable (HeadNo, ActualCode)
SELECT 
    s.HeadNo,
    CONCAT(s.HeadNo, FORMAT(n.num, '0000')) AS ActualCode
FROM SourceTable s
CROSS JOIN nums n
OPTION (MAXRECURSION 10000);

3. PostgreSQL #

PostgreSQL自带generate_series函数，生成序列更简洁：

INSERT INTO TargetTable (headno, actualcode)
SELECT 
    s.headno,
    CONCAT(s.headno, LPAD(n.num::TEXT, 4, '0')) AS actualcode
FROM SourceTable s
CROSS JOIN generate_series(0, 9999) AS n(num);

4. 老版本MySQL（不支持CTE） #

如果你的MySQL版本不支持递归CTE，可以先创建一个数字辅助表，再进行关联：

-- 第一步：创建数字辅助表
CREATE TABLE nums (num INT PRIMARY KEY);

-- 第二步：插入0-9999的所有数字
INSERT INTO nums (num)
SELECT a.num + b.num*10 + c.num*100 + d.num*1000
FROM (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
     (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
     (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c,
     (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d;

-- 第三步：关联生成编码并插入
INSERT INTO TargetTable (HeadNo, ActualCode)
SELECT s.HeadNo, CONCAT(s.HeadNo, LPAD(n.num, 4, '0')) AS ActualCode
FROM SourceTable s
CROSS JOIN nums n;

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