SQL实现：按目标数据的重复行数插入行数据

需求回顾 #

现有Product表（Id为自增主键，Name为普通字段），初始数据如下：

另有含重复值的目标数据：

需要将Product表补充为每个Name的行数与目标数据一致，仅新增缺失的行（不删除现有数据，比如three需保留），最终结果如下：

实现思路 #

1. 统计目标数据中每个Name的总需求数量
2. 统计Product表中每个Name的已存在数量
3. 计算每个Name需要新增的行数（需求数 - 已存在数，仅处理结果大于0的情况）
4. 生成对应数量的重复行，批量插入到Product表中

SQL实现（SQL Server版本） #

-- 1. 构造目标数据（若已有目标表，直接替换为目标表名即可）
WITH TargetData AS (
    SELECT Name FROM (
        VALUES ('one'), ('two'), ('two'), ('two'), ('four')
    ) AS T(Name)
),
-- 2. 统计目标数据中每个Name的需求数量
TargetCounts AS (
    SELECT Name, COUNT(*) AS TargetQty
    FROM TargetData
    GROUP BY Name
),
-- 3. 统计现有Product表中每个Name的已存在数量
CurrentCounts AS (
    SELECT Name, COUNT(*) AS CurrentQty
    FROM Product
    GROUP BY Name
),
-- 4. 计算需要新增的行数
NeedInsert AS (
    SELECT 
        COALESCE(t.Name, c.Name) AS Name,
        COALESCE(t.TargetQty, 0) - COALESCE(c.CurrentQty, 0) AS InsertQty
    FROM TargetCounts t
    FULL JOIN CurrentCounts c ON t.Name = c.Name
    WHERE COALESCE(t.TargetQty, 0) - COALESCE(c.CurrentQty, 0) > 0
)
-- 5. 生成需要插入的重复行并插入到Product表
INSERT INTO Product (Name)
SELECT ni.Name
FROM NeedInsert ni
JOIN (
    -- 生成数字序列，覆盖最大需要插入的数量
    SELECT TOP (SELECT MAX(InsertQty) FROM NeedInsert) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Num
    FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
) nums ON nums.Num <= ni.InsertQty;

MySQL版本实现 #

-- 1. 构造目标数据
WITH RECURSIVE TargetData AS (
    SELECT 'one' AS Name UNION ALL
    SELECT 'two' UNION ALL
    SELECT 'two' UNION ALL
    SELECT 'two' UNION ALL
    SELECT 'four'
),
-- 2. 统计目标需求数量
TargetCounts AS (
    SELECT Name, COUNT(*) AS TargetQty
    FROM TargetData
    GROUP BY Name
),
-- 3. 统计现有数量
CurrentCounts AS (
    SELECT Name, COUNT(*) AS CurrentQty
    FROM Product
    GROUP BY Name
),
-- 4. 计算需要新增的行数
NeedInsert AS (
    SELECT 
        COALESCE(t.Name, c.Name) AS Name,
        COALESCE(t.TargetQty, 0) - COALESCE(c.CurrentQty, 0) AS InsertQty
    FROM TargetCounts t
    FULL JOIN CurrentCounts c ON t.Name = c.Name
    WHERE COALESCE(t.TargetQty, 0) - COALESCE(c.CurrentQty, 0) > 0
),
-- 生成数字序列（递归方式）
Numbers AS (
    SELECT 1 AS Num
    UNION ALL
    SELECT Num + 1 FROM Numbers WHERE Num < (SELECT MAX(InsertQty) FROM NeedInsert)
)
-- 插入数据
INSERT INTO Product (Name)
SELECT ni.Name
FROM NeedInsert ni
JOIN Numbers n ON n.Num <= ni.InsertQty;

关键说明 #

• 若目标数据存储在已有表中，直接将TargetData替换为目标表名即可
• Id字段如果是自增主键，插入时无需指定，数据库会自动生成连续的主键值，与示例结果一致
• 该方案仅新增需要的行，不会删除现有Product表中的数据（比如three会保留）

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