如何使用递归SQL基于父子关系表生成指定数量的行

针对你的需求，我们可以通过**递归CTE（公共表表达式）**结合数字序列来生成符合层级关联规则的行数据，具体实现如下：

完整SQL代码 #

-- 生成用于控制行数的数字序列
WITH Numbers AS (
    SELECT 1 AS N
    UNION ALL
    SELECT N + 1 FROM Numbers WHERE N < (SELECT MAX(NumberOfRows) FROM #Temp3)
),
-- 递归生成层级关联的目标行
HierarchyRows AS (
    -- 锚点：处理顶层节点（ParentId为NULL的Shelve）
    SELECT 
        t.Id AS OriginalId,
        t.LocationName,
        CAST(t.LocationName + '_' + CAST(n.N AS VARCHAR(10)) AS NVARCHAR(20)) AS InstanceId,
        NULL AS ParentInstanceId,
        1 AS Level
    FROM #Temp3 t
    CROSS JOIN Numbers n
    WHERE t.ParentId IS NULL
        AND n.N <= t.NumberOfRows

    UNION ALL

    -- 递归：处理子节点，关联父节点生成的行
    SELECT 
        t.Id AS OriginalId,
        t.LocationName,
        CAST(t.LocationName + '_' + hr.InstanceId + '_' + CAST(n.N AS VARCHAR(10)) AS NVARCHAR(50)) AS InstanceId,
        hr.InstanceId AS ParentInstanceId,
        hr.Level + 1 AS Level
    FROM #Temp3 t
    JOIN HierarchyRows hr ON t.ParentId = hr.OriginalId
    CROSS JOIN Numbers n
    WHERE n.N <= t.NumberOfRows
)
SELECT * FROM HierarchyRows
ORDER BY Level, ParentInstanceId, InstanceId;

方案说明 #

1. 数字序列CTE（Numbers）：
   递归生成从1到#Temp3表中最大NumberOfRows的数字集合，用来控制每个节点需要生成的行数，确保覆盖所有层级的行数需求。

2. 递归CTE（HierarchyRows）：

   • 锚点成员：筛选出顶层节点（ParentId IS NULL的Shelve），通过CROSS JOIN数字序列生成指定的2行数据，每个行的InstanceId采用[节点名]_[序号]的格式，清晰标识每个顶层实例。
   • 递归成员：关联父节点已生成的行数据，结合当前子节点的NumberOfRows，通过CROSS JOIN数字序列为每个父实例生成对应数量的子节点行。例如每个Shelve实例生成10个Rack，每个Rack实例生成20个Bin，自动维护层级关联关系。

3. 结果输出：
   最终查询按层级、父实例ID、实例ID排序，便于直观查看各层级的关联关系。

执行后会生成：

• 2行Shelve数据（Level 1）
• 20行Rack数据（每个Shelve对应10个，Level 2）
• 400行Bin数据（每个Rack对应20个，Level 3）

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