如何基于父ID关联的层级表为每个父级创建含子级列的表

我来帮你搞定这个需求哈！你已经能用CTE遍历父子层级了，接下来核心就是动态生成建表语句——因为每个父级的子级数量、名称都可能不一样，得根据实际数据自动生成。下面我分步骤拆解，还附上不同数据库的代码示例。

第一步：完善你的层级遍历CTE #

先把你的CTE补全（假设你的表叫ComplexType，顶级父级的ParentID为NULL，你可以根据自己的表结构调整）：

WITH ParentsAndItsChilds (ParentID, ChildName, ChildID, ParentName, Depth) AS (
    -- 锚点：取所有顶级父级
    SELECT 
        ComplexType.ParentID, 
        ComplexType.Name AS ChildName, 
        ComplexType.ID AS ChildID, 
        NULL AS ParentName, 
        0 AS Depth
    FROM ComplexType
    WHERE ParentID IS NULL -- 这里根据你的表结构改，比如有的表用0表示顶级
    UNION ALL
    -- 递归：关联子级
    SELECT 
        c.ParentID, 
        c.Name AS ChildName, 
        c.ID AS ChildID, 
        p.ChildName AS ParentName, 
        p.Depth + 1 AS Depth
    FROM ComplexType c
    INNER JOIN ParentsAndItsChilds p ON c.ParentID = p.ChildID
)
SELECT * FROM ParentsAndItsChilds;

第二步：动态生成建表语句 #

这里分两种常见数据库给出示例，核心思路都是先遍历所有父级，再根据每个父级的子级列表生成对应的CREATE TABLE语句。

示例1：SQL Server 版本 #

用游标遍历每个父级，动态生成建表和插入数据的SQL：

-- 声明变量
DECLARE @ParentID INT, @ParentName NVARCHAR(100), @ChildColumns NVARCHAR(MAX), @ChildCount INT;
DECLARE @CreateSQL NVARCHAR(MAX), @InsertSQL NVARCHAR(MAX);

-- 游标获取所有有直接子级的父级（Depth=1表示直接子级，若要所有层级子级可调整）
DECLARE ParentCursor CURSOR FOR
SELECT 
    ParentID,
    ParentName,
    -- 把子级名称转成合法列名，用QUOTENAME避免特殊字符
    STRING_AGG(QUOTENAME(ChildName), ', ') AS ChildColumns,
    COUNT(ChildID) AS ChildCount
FROM ParentsAndItsChilds
WHERE Depth = 1
GROUP BY ParentID, ParentName;

OPEN ParentCursor;
FETCH NEXT FROM ParentCursor INTO @ParentID, @ParentName, @ChildColumns, @ChildCount;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 1. 生成建表语句，表名用父ID区分，比如Parent_1、Parent_2
    SET @CreateSQL = N'CREATE TABLE dbo.Parent_' + CAST(@ParentID AS NVARCHAR(10)) + N'(
        ParentID INT PRIMARY KEY,
        ParentName NVARCHAR(100),
        ' + @ChildColumns + N' NVARCHAR(100) -- 子级列类型根据实际调整，比如INT/DECIMAL等
    );';
    EXEC sp_executesql @CreateSQL;

    -- 2. 生成插入数据的语句，用PIVOT把父级的子级转成列值
    SET @InsertSQL = N'
        WITH PivotedChilds AS (
            SELECT 
                ParentID,
                ParentName,
                ChildID,
                -- 给重复子级名称加序号，避免列名冲突
                QUOTENAME(ChildName + ''_'' + CAST(ROW_NUMBER() OVER(PARTITION BY ParentID, ChildName ORDER BY ChildID) AS NVARCHAR(10))) AS ColumnAlias
            FROM ParentsAndItsChilds
            WHERE ParentID = ' + CAST(@ParentID AS NVARCHAR(10)) + N' AND Depth = 1
        )
        INSERT INTO dbo.Parent_' + CAST(@ParentID AS NVARCHAR(10)) + N'
        SELECT ParentID, ParentName, ' + @ChildColumns + N'
        FROM PivotedChilds
        PIVOT (
            MAX(ChildID) FOR ColumnAlias IN (' + @ChildColumns + N')
        ) AS PivotTable;';
    EXEC sp_executesql @InsertSQL;

    FETCH NEXT FROM ParentCursor INTO @ParentID, @ParentName, @ChildColumns, @ChildCount;
END

CLOSE ParentCursor;
DEALLOCATE ParentCursor;

示例2：MySQL 版本 #

MySQL用GROUP_CONCAT拼接SQL，再用PREPARE/EXECUTE执行：

-- 先拼接所有建表语句
SET @createSql = '';
SELECT GROUP_CONCAT(
    CONCAT(
        'CREATE TABLE Parent_', ParentID, ' (
            ParentID INT PRIMARY KEY,
            ParentName VARCHAR(100),
            ', GROUP_CONCAT(QUOTE(ChildName) SEPARATOR ' VARCHAR(100), '), ' VARCHAR(100)
        );'
    )
) INTO @createSql
FROM (
    SELECT DISTINCT ParentID, ParentName, ChildName
    FROM ParentsAndItsChilds
    WHERE Depth = 1
) AS ChildsPerParent
GROUP BY ParentID, ParentName;

-- 执行建表语句
PREPARE stmt FROM @createSql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 插入数据（用动态SQL实现PIVOT逻辑）
SET @insertSql = '';
SELECT GROUP_CONCAT(
    CONCAT(
        'INSERT INTO Parent_', ParentID, '
        SELECT * FROM (
            SELECT ParentID, ParentName, ChildID, ChildName
            FROM ParentsAndItsChilds
            WHERE ParentID = ', ParentID, ' AND Depth = 1
        ) AS t
        PIVOT (
            MAX(ChildID) FOR ChildName IN (', GROUP_CONCAT(QUOTE(ChildName) SEPARATOR ', '), ')
        ) AS p;'
    )
) INTO @insertSql
FROM (
    SELECT DISTINCT ParentID, ParentName, ChildName
    FROM ParentsAndItsChilds
    WHERE Depth = 1
) AS ChildsPerParent
GROUP BY ParentID, ParentName;

PREPARE stmt FROM @insertSql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意事项 #

• 列名冲突：如果同一个父级下有重复的子级名称，记得给列名加序号（比如ChildName_1），避免建表报错。
• 数据类型：子级列的类型要和原表中对应字段一致，比如原表子级是数字就用INT，是文本就用VARCHAR/NVARCHAR。
• 层级范围：如果需要包含所有层级的子级（比如父级→子级→孙级），可以调整CTE的Depth条件，或者先把所有层级的子级扁平化后再生成列。

内容的提问来源于stack exchange，提问作者Yaman Alsaadi jbawi