SQL无需游标按关联计数批量插入Reports表重复行实现方法

实现思路 #

通过构造连续数字序列和统计得到的重复次数做笛卡尔关联，自动生成对应条数的待插入数据，全程不需要使用游标。

SQL Server 版本（适配你当前使用的语法） #

WITH RepeatCounts AS (
    -- 统计每个报表需要复制的次数 = 关联的调度数 - 1
    SELECT ReportId, COUNT(*) - 1 AS RepeatTimes
    FROM [ScheduledReports]
    GROUP BY ReportId
    HAVING COUNT(*) > 1
),
NumSequence AS (
    -- 递归生成连续数字序列，上限为最大需要重复的次数
    SELECT 1 AS Num
    UNION ALL
    SELECT Num + 1 
    FROM NumSequence 
    WHERE Num < (SELECT MAX(RepeatTimes) FROM RepeatCounts)
)
INSERT INTO Reports (Description /* 其他需要复制的字段请自行补充到此处 */)
SELECT r.Description /* 和上方插入字段一一对应即可 */
FROM Reports r
INNER JOIN RepeatCounts rc 
    ON r.Id = rc.ReportId
INNER JOIN NumSequence ns 
    ON ns.Num <= rc.RepeatTimes
-- 如果单条报表最大重复次数超过100，必须添加以下配置取消递归层数限制
OPTION (MAXRECURSION 0);

其他数据库适配 #

PostgreSQL

利用内置的generate_series函数简化实现：

WITH RepeatCounts AS (
    SELECT ReportId, COUNT(*) - 1 AS RepeatTimes
    FROM ScheduledReports
    GROUP BY ReportId
    HAVING COUNT(*) > 1
)
INSERT INTO Reports (Description)
SELECT r.Description
FROM Reports r
JOIN RepeatCounts rc ON r.Id = rc.ReportId
JOIN generate_series(1, (SELECT MAX(RepeatTimes) FROM RepeatCounts)) AS t(n) 
ON t.n <= rc.RepeatTimes;

MySQL 8.0+

WITH RECURSIVE RepeatCounts AS (
    SELECT ReportId, COUNT(*) - 1 AS RepeatTimes
    FROM ScheduledReports
    GROUP BY ReportId
    HAVING COUNT(*) > 1
),
NumSequence AS (
    SELECT 1 AS Num
    UNION ALL
    SELECT Num + 1 FROM NumSequence 
    WHERE Num < (SELECT MAX(RepeatTimes) FROM RepeatCounts)
)
INSERT INTO Reports (Description)
SELECT r.Description
FROM Reports r
INNER JOIN RepeatCounts rc ON r.Id = rc.ReportId
INNER JOIN NumSequence ns ON ns.Num <= rc.RepeatTimes;

后续操作提示 #

插入重复报表记录后，你还需要更新ScheduledReports表的ReportId外键，把原来重复关联的调度记录绑定到新生成的报表ID上，才能最终完成一对一关联的改造。 #

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