基于递归查询的存储过程优化：单次输入返回完整关联结果

我懂你的需求啦——现在你得手动多次调用存储过程才能拿到完整的100、101、102节点列表，想要改成传任意一个（或多个）ID就能一次性获取所有关联节点。问题出在你当前的Ancestors函数只是做了单次查询，没有真正实现递归遍历所有层级的关联关系。

咱们来修改代码，用递归CTE自动遍历所有父子关联，直到没有新节点可以加入为止，这样就能一次性拿到全部结果，不用手动重复调用。

第一步：修改Ancestors函数 #

我们把函数改成递归CTE的形式，同时保留对逗号分隔多ID的支持（用你已有的SplitInts函数拆分输入）：

CREATE FUNCTION dbo.Ancestors (@List VARCHAR(MAX)) 
RETURNS TABLE 
AS RETURN
WITH SplitIDs AS (
    -- 拆分传入的逗号分隔ID列表
    SELECT CAST(Item AS VARCHAR(MAX)) AS ID FROM dbo.SplitInts(@List, ',')
),
RecursiveCTE AS (
    -- 初始步骤：获取所有传入ID，以及它们直接关联的父/子节点
    SELECT DISTINCT 
        COALESCE(e.Parent, e.Child) AS RESULT
    FROM example e
    INNER JOIN SplitIDs s ON e.Parent = s.ID OR e.Child = s.ID
    UNION
    SELECT ID AS RESULT FROM SplitIDs -- 确保传入的ID本身都被包含进去

    UNION ALL

    -- 递归步骤：在上一轮结果的基础上，继续查找关联的父/子节点
    SELECT DISTINCT 
        COALESCE(e.Parent, e.Child) AS RESULT
    FROM example e
    INNER JOIN RecursiveCTE r ON e.Parent = r.RESULT OR e.Child = r.RESULT
    -- 过滤掉已经在结果里的节点，避免重复和无限递归
    WHERE COALESCE(e.Parent, e.Child) NOT IN (SELECT RESULT FROM RecursiveCTE)
)
SELECT DISTINCT RESULT FROM RecursiveCTE;
GO

第二步：存储过程保持不变 #

你的GetAncestors存储过程可以直接调用修改后的函数，不需要额外改动：

CREATE PROCEDURE GetAncestors (@thingID VARCHAR(MAX)) 
AS
SELECT RESULT FROM dbo.Ancestors(@thingID);
GO

测试验证 #

现在不管你传入单个ID还是多个ID，都能一次性拿到所有关联节点：

EXEC GetAncestors @thingID = '100';

执行结果：100, 101, 102

EXEC GetAncestors @thingID = '101';

执行结果：100, 101, 102

EXEC GetAncestors @thingID = '102';

执行结果：100, 101, 102

EXEC GetAncestors @thingID = '100,101';

执行结果：100, 101, 102

代码逻辑说明 #

• SplitIDs CTE：用你已有的SplitInts函数把输入的逗号分隔ID列表拆分成单个ID，兼容单个或多个ID的输入场景。
• 递归CTE初始部分：先获取传入ID直接关联的父/子节点，同时把传入ID本身加入结果，避免遗漏起始节点。
• 递归部分：每次用上一轮的结果去匹配表中的父子关系，把新找到的节点加入结果集，直到没有新节点可以加入（通过NOT IN过滤已存在的节点，防止重复和无限循环）。
• DISTINCT关键字：确保最终结果里没有重复的节点。

这样就实现了你想要的自动递归逻辑，不用手动多次调用存储过程，一次调用就能拿到所有关联的节点啦~

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