MS SQL递归查询Analogues表关联数据遗漏结果求助

原表信息 #

表名：Analogues，数据如下：

需求是递归查询与ID-Items=11相关的所有类似项，但原有SQL未检索到ID=10的关联记录，期望结果如下：

期望结果 #

问题分析 #

原有SQL的递归逻辑仅单向匹配ib2.[ID-Items analogs] = s.[ID-Items]，无法遍历反向关联的节点（比如从2到10的关联），导致部分类似项遗漏。

解决方案 #

修正后的递归CTE会双向遍历所有关联节点，并通过跟踪已访问节点避免循环：

DECLARE @ID AS BIGINT = 11;

WITH SourceCTE AS (
    -- 初始节点：获取所有直接关联目标ID的记录
    SELECT 
        ib.[ID-Items], 
        ib.[ID-Items analogs],
        -- 记录已访问的节点，用逗号分隔避免匹配错误
        CAST(',' + CAST(ib.[ID-Items] AS VARCHAR(10)) + ',' + CAST(ib.[ID-Items analogs] AS VARCHAR(10)) + ',' AS VARCHAR(MAX)) AS Visited
    FROM 
        [Analogues] ib
    WHERE 
        ib.[ID-Items] = @ID OR ib.[ID-Items analogs] = @ID

    UNION ALL

    SELECT 
        ib2.[ID-Items], 
        ib2.[ID-Items analogs],
        -- 更新已访问节点列表
        CAST(s.Visited + CAST(ib2.[ID-Items] AS VARCHAR(10)) + ',' + CAST(ib2.[ID-Items analogs] AS VARCHAR(10)) + ',' AS VARCHAR(MAX)) AS Visited
    FROM 
        [Analogues] ib2
    INNER JOIN 
        SourceCTE s ON 
        -- 双向匹配：当前记录任意一端在已访问列表，另一端未被访问
        (CHARINDEX(',' + CAST(ib2.[ID-Items] AS VARCHAR(10)) + ',', s.Visited) > 0 
         AND CHARINDEX(',' + CAST(ib2.[ID-Items analogs] AS VARCHAR(10)) + ',', s.Visited) = 0)
        OR 
        (CHARINDEX(',' + CAST(ib2.[ID-Items analogs] AS VARCHAR(10)) + ',', s.Visited) > 0 
         AND CHARINDEX(',' + CAST(ib2.[ID-Items] AS VARCHAR(10)) + ',', s.Visited) = 0)
)
-- 去重后输出结果
SELECT DISTINCT 
    [ID-Items], 
    [ID-Items analogs]
FROM 
    SourceCTE
ORDER BY 
    [ID-Items], 
    [ID-Items analogs]
OPTION(MAXRECURSION 100);

关键改进点 #

1. 双向关联遍历：递归条件覆盖了记录两端与已访问节点的匹配，确保所有关联节点都被遍历
2. 循环避免：通过Visited字段跟踪已处理的节点，防止递归陷入循环
3. 去重优化：用DISTINCT替代GROUP BY，逻辑更直观

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