如何通过SQL获取所有关联ID的全量多层亲属关系

我有一组基于字段（驾照号、社保号等）匹配关联的ID数据，数据无明确层级，已将Parent设为较小ID避免递归死循环。尝试用递归CTE获取各ID的层级关系，但无法覆盖所有间接关联（如ID10与71的关联），需要能返回所有关联ID两两组合的方案。

示例数据 #

ID  | ParentID
----|---------
10  | 7
7   | 5
5   | 3
71  | 5

尝试的递归CTE代码 #

WITH RecursiveCTE AS (
    SELECT 
        ID, 
        ParentID, 
        ID AS RootID
    FROM YourTable
    WHERE ParentID IS NULL OR ParentID = ID
    UNION ALL
    SELECT 
        t.ID, 
        t.ParentID, 
        r.RootID
    FROM YourTable t
    JOIN RecursiveCTE r ON t.ParentID = r.ID
)
SELECT * FROM RecursiveCTE;

期望输出 #

需要返回所有关联ID的两两组合，包括所有直接和间接关联的配对，示例如下：

ID1 | ID2
----|----
10  | 7
10  | 5
10  | 3
10  | 71
7   | 10
7   | 5
7   | 3
7   | 71
5   | 10
5   | 7
5   | 3
5   | 71
3   | 10
3   | 7
3   | 5
3   | 71
71  | 10
71  | 7
71  | 5
71  | 3

最终解决方案：WHILE循环实现高效全量关联 #

递归CTE无法覆盖跨分支的间接关联，改用WHILE循环可以逐步扩展所有关联关系，直到没有新的配对产生：

-- 创建临时表存储所有关联对
CREATE TABLE #Associations (
    ID1 INT,
    ID2 INT,
    PRIMARY KEY (ID1, ID2)
);

-- 插入初始直接关联（包含反向配对）
INSERT INTO #Associations
SELECT ID, ParentID FROM YourTable WHERE ParentID IS NOT NULL
UNION
SELECT ParentID, ID FROM YourTable WHERE ParentID IS NOT NULL;

-- 循环迭代，扩展间接关联
WHILE EXISTS (
    SELECT 1
    FROM #Associations a1
    JOIN #Associations a2 ON a1.ID2 = a2.ID1
    LEFT JOIN #Associations a3 ON a1.ID1 = a3.ID1 AND a2.ID2 = a3.ID2
    WHERE a3.ID1 IS NULL
)
BEGIN
    INSERT INTO #Associations (ID1, ID2)
    SELECT DISTINCT a1.ID1, a2.ID2
    FROM #Associations a1
    JOIN #Associations a2 ON a1.ID2 = a2.ID1
    LEFT JOIN #Associations a3 ON a1.ID1 = a3.ID1 AND a2.ID2 = a3.ID2
    WHERE a3.ID1 IS NULL;
END

-- 查询最终全量关联配对
SELECT * FROM #Associations;

-- 清理临时表
DROP TABLE #Associations;

内容的提问来源于stack exchange，提问作者Eric G