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

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

1. 循环路径检测与修复

在处理员工数据时，可能会出现循环路径的问题。例如，在输出中第二次访问到员工 1 时，检测到了循环路径 .1.3.7.9.14.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, 
    -- 显然根节点没有循环 
    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)), 
    -- 如果父节点的路径包含子节点的 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 
) 
SELECT pa