SQL Server Graph DB如何查询以指定节点为起止的所有路径

SQL Server 原生图查询没有提供ALL_PATH语法，要获取所有符合条件的路径，可通过递归CTE实现，以下是对应需求的具体实现：

需求1：查询所有以节点C为起点的路径 #

WITH AllPaths AS (
    -- 递归初始项：起点为C
    SELECT 
        userName AS StartNode,
        CAST(userName AS VARCHAR(1000)) AS Path,
        $node_id AS CurrentNodeId,
        1 AS Level
    FROM Person 
    WHERE userName = 'C'
    UNION ALL
    -- 递归项：沿着Follow边向下遍历
    SELECT 
        ap.StartNode,
        CAST(CONCAT(ap.Path, '->', p.userName) AS VARCHAR(1000)) AS Path,
        p.$node_id AS CurrentNodeId,
        ap.Level + 1 AS Level
    FROM AllPaths ap
    JOIN Follow f ON f.$from_id = ap.CurrentNodeId
    JOIN Person p ON p.$node_id = f.$to_id
    -- 防止循环遍历：避免重复访问路径中已存在的节点
    WHERE CHARINDEX(p.userName, ap.Path) = 0
    -- 可自定义最大路径长度，避免递归过深
    AND ap.Level < 10
)
SELECT StartNode, Path, Level 
FROM AllPaths
ORDER BY Level, Path;

需求2：查询所有以节点C为终点的路径 #

WITH AllPaths AS (
    -- 递归初始项：终点为C
    SELECT 
        userName AS EndNode,
        CAST(userName AS VARCHAR(1000)) AS Path,
        $node_id AS CurrentNodeId,
        1 AS Level
    FROM Person 
    WHERE userName = 'C'
    UNION ALL
    -- 递归项：沿着Follow边向上溯源
    SELECT 
        ap.EndNode,
        CAST(CONCAT(p.userName, '->', ap.Path) AS VARCHAR(1000)) AS Path,
        p.$node_id AS CurrentNodeId,
        ap.Level + 1 AS Level
    FROM AllPaths ap
    JOIN Follow f ON f.$to_id = ap.CurrentNodeId
    JOIN Person p ON p.$node_id = f.$from_id
    WHERE CHARINDEX(p.userName, ap.Path) = 0
    AND ap.Level < 10
)
SELECT Path, EndNode, Level 
FROM AllPaths
ORDER BY Level, Path;

核心需求：查询所有从C出发最终回到C的闭环路径 #

在需求1的递归基础上增加终点过滤即可：

WITH CyclePaths AS (
    SELECT 
        userName AS StartNode,
        CAST(userName AS VARCHAR(1000)) AS Path,
        $node_id AS CurrentNodeId,
        1 AS Level
    FROM Person 
    WHERE userName = 'C'
    UNION ALL
    SELECT 
        cp.StartNode,
        CAST(CONCAT(cp.Path, '->', p.userName) AS VARCHAR(1000)) AS Path,
        p.$node_id AS CurrentNodeId,
        cp.Level + 1 AS Level
    FROM CyclePaths cp
    JOIN Follow f ON f.$from_id = cp.CurrentNodeId
    JOIN Person p ON p.$node_id = f.$to_id
    -- 除了回到C的情况，其余路径不允许重复节点
    WHERE (CHARINDEX(p.userName, cp.Path) = 0 OR p.userName = 'C')
    AND cp.Level < 10
)
SELECT Path AS CyclePath, Level AS CycleLength
FROM CyclePaths
WHERE CurrentNodeId = (SELECT $node_id FROM Person WHERE userName = 'C')
-- 过滤掉长度为1的无效自环
AND Level > 1
ORDER BY Level, Path;

补充说明 #

• 代码中Level < 10是为了限制最大递归深度，可根据实际业务场景调整，默认不能超过SQL Server递归CTE上限100，如需更高可通过MAXRECURSION提示指定
• 用CHARINDEX判断节点是否已存在路径中是简易实现，如用户名存在包含关系可改用JSON数组、临时表等方式存储已访问节点

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