如何用CTE获取所有关联ID（无视关联层级）

这个需求完全可以实现，本质是查询无向图中的连通分量——找到与指定节点直接或间接相连的所有节点。递归CTE的终止条件就是当递归迭代无法再找到新的未访问过的关联ID时，自动停止。

具体实现SQL #

以下是针对该场景的递归CTE查询代码，你可以替换@TargetPersonId的值来查询不同ID的关联集合：

DECLARE @TargetPersonId INT = 5; -- 替换为你要查询的目标ID

WITH RecursiveRelationships AS (
    -- 锚点成员：初始节点，同时记录已访问的节点集合
    SELECT 
        Person_Id AS ConnectedId,
        CAST(CONCAT(',', Person_Id, ',') AS VARCHAR(MAX)) AS VisitedIds
    FROM (
        SELECT Person_Id_1 AS Person_Id FROM #Relationships WHERE Person_Id_2 = @TargetPersonId
        UNION
        SELECT Person_Id_2 AS Person_Id FROM #Relationships WHERE Person_Id_1 = @TargetPersonId
        UNION
        SELECT @TargetPersonId -- 包含自身（如果需要排除可去掉这行）
    ) AS Initial
    UNION ALL
    -- 递归成员：查找所有未访问过的关联节点
    SELECT 
        CASE 
            WHEN r.Person_Id_1 = rr.ConnectedId THEN r.Person_Id_2 
            ELSE r.Person_Id_1 
        END AS ConnectedId,
        CAST(CONCAT(rr.VisitedIds, CASE 
            WHEN r.Person_Id_1 = rr.ConnectedId THEN r.Person_Id_2 
            ELSE r.Person_Id_1 
        END, ',') AS VARCHAR(MAX)) AS VisitedIds
    FROM RecursiveRelationships rr
    JOIN #Relationships r ON 
        (r.Person_Id_1 = rr.ConnectedId OR r.Person_Id_2 = rr.ConnectedId)
    WHERE 
        -- 确保当前节点未被访问过，避免循环
        CHARINDEX(CONCAT(',', CASE 
            WHEN r.Person_Id_1 = rr.ConnectedId THEN r.Person_Id_2 
            ELSE r.Person_Id_1 
        END, ','), rr.VisitedIds) = 0
)
-- 去重后返回所有关联ID（排除自身的话加WHERE ConnectedId != @TargetPersonId）
SELECT STRING_AGG(ConnectedId, ', ') AS AssociatedIds
FROM (
    SELECT DISTINCT ConnectedId FROM RecursiveRelationships
) AS DistinctIds
WHERE ConnectedId != @TargetPersonId; -- 排除自身，不需要可删除此行

代码说明 #

• 锚点成员：首先找到与目标ID直接关联的节点，同时初始化已访问节点的字符串（用逗号包裹避免匹配部分ID，比如区分1和10）。
• 递归成员：每次从已访问的节点出发，找到所有关联的新节点，并且检查该节点是否已经在已访问集合中，避免无限循环。
• 最终结果：通过STRING_AGG将所有关联ID拼接成字符串，也可以直接返回单个ID的列表。

示例测试 #

• 当@TargetPersonId=5时，返回结果：1, 2, 3, 4, 6
• 当@TargetPersonId=1时，返回结果：2, 3, 4, 5, 6
• 当@TargetPersonId=8时，返回结果：7, 9, 10

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