62、图与递归查询：物化路径解决方案详解

图与递归查询：物化路径解决方案详解

1. 循环路径检测与修复

在处理图数据时，可能会遇到循环路径的问题。例如，在员工关系图中，当第二次到达员工 1 时，就检测到了一个循环。以下是检测循环路径的代码：

DECLARE @root AS INT = 1; 

WITH Subs AS 
( 
  SELECT empid, empname, 0 AS lvl, 
    CAST('.' + CAST(empid AS VARCHAR(10)) + '.' AS VARCHAR(MAX)) AS path, 
    -- Obviously root has no cycle 
    0 AS cycle 
  FROM dbo.Employees 
  WHERE empid = @root 

  UNION ALL 

  SELECT C.empid, C.empname, P.lvl + 1, 
    CAST(P.path + CAST(C.empid AS VARCHAR(10)) + '.' AS VARCHAR(MAX)), 
    -- Cycle detected if parent's path contains child's id 
    CASE WHEN P.path LIKE '%.' + CAST(C.empid AS VARCHAR(10)) + '.%' THEN 1 ELSE 0 END 
  FROM Subs AS P 
    INNER JOIN dbo.Employees AS C 
      ON C.mgrid = P.empid 
         AND P.cycle = 0