如何为具有多个父项的子项创建SQL Server CTE以返回父子记录

-- 创建临时表，包含父项和子项的关系
CREATE TABLE #ParentChild
(
    ParentID INT,
    ChildID INT
)

-- 插入数据
INSERT INTO #ParentChild (ParentID, ChildID)
VALUES (1, 2), (1, 3), (2, 4), (2, 5), (3, 6), (3, 7)

-- 使用递归 CTE 查询所有子项的父项
;WITH RecursiveCTE (ParentID, ChildID, Level)
AS
(
    SELECT PC.ParentID, PC.ChildID, 1 AS Level
    FROM #ParentChild PC
    WHERE PC.ParentID IS NOT NULL

    UNION ALL

    SELECT R.ParentID, PC.ChildID, R.Level + 1 AS Level
    FROM #ParentChild PC
    INNER JOIN RecursiveCTE R ON PC.ParentID = R.ChildID
)
SELECT * FROM RecursiveCTE
ORDER BY ParentID, ChildID

-- 删除临时表
DROP TABLE #ParentChild