将递归CTE穿越到SQL Server中的根部达到最大递归

Question

我有一组典型的员工和相应的经理，因为大多数递归CTE教程使用。 I took it from Uri Dimant's answer in

与Depth-First搜索不同，我的目标是输入员工ID和一个员工ID和查询返回经理列表。
因此，我将加入语句调整以将Manager ID从CTE到员工ID。它应该为某个员工提供经理的姓名。

导致错误：
终止语句。在语句完成之前，最大递归100已经用尽。

我认为，当递归达到经理的最高等级时，它将返回一个空的结果集，指示递归的结束。

我想了解SQL引擎如何知道何时停止。 以及如何使此查询如我预期的那样有效。

谢谢

IF OBJECT_ID('Employees') IS NULL
BEGIN
    CREATE TABLE Employees
    (
    empid   int         NOT NULL,
    mgrid   int         NULL,
    empname nvarchar(25) NOT NULL,
    salary  money       NOT NULL,
    CONSTRAINT PK_Employees PRIMARY KEY(empid),
    CONSTRAINT FK_Employees_mgrid_empid
      FOREIGN KEY(mgrid)
      REFERENCES Employees(empid)
    )


    CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
    SET NOCOUNT ON


    INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)
    INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)
    INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)
    INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00) 
    INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)
    INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)
    INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00)
    INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00)
    INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00)
    INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00)
    INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)
    INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)
    INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)
    INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)

END
GO

WITH EmpCTE
AS
( 

  -- Anchor Member (AM)
  SELECT 
      empid, 
      empname, 
      mgrid, 
      0 AS level                            -- <------------------- SET LVL START FROM 0
  
  FROM Employees
  WHERE EMPID = 7

  UNION ALL

  -- Recursive Member (RM)
  SELECT 
      e.empid, 
      e.empname, 
      e.mgrid, 
      e.level+1                     -- <------------------- INCREMENT LVL

manager id
  FROM Employees AS m
    JOIN EmpCTE AS e                    -- <------------------- RECURSIVELY CALL EmpCTE
      ON e.mgrid = m.empid
)

SELECT * FROM EmpCTE 

Answer 1

您已经设法创建了一个无限的循环。您可以在级别中粘贴过滤器，以调试以下内容：（

删除Manager ID之后）

WITH EmpCTE
AS
( 
  -- Anchor Member (AM)
  SELECT 
      empid, 
      empname, 
      mgrid, 
      0 AS level                            -- <------------------- SET LVL START FROM 0
  FROM Employees
  WHERE EMPID = 7
  UNION ALL
  -- Recursive Member (RM)
  SELECT 
      e.empid, 
      e.empname, 
      e.mgrid, 
      e.level+1                     -- <------------------- INCREMENT LVL
  FROM Employees AS m
    JOIN EmpCTE AS e                    -- <------------------- RECURSIVELY CALL EmpCTE
      ON e.mgrid = m.empid
where level < 2
)
SELECT * FROM EmpCTE;

empid       empname                   mgrid       level
----------- ------------------------- ----------- -----------
7           Robert                    3           0
7           Robert                    3           1
7           Robert                    3           2

这是因为您将列从empcte投射为e < /code>而不是员工作为M，因此您只是一次又一次获得相同的数据（加上级别的增加）。

WITH EmpCTE
AS
( 
  -- Anchor Member (AM)
  SELECT 
      empid, 
      empname, 
      mgrid, 
      0 AS level                            -- <------------------- SET LVL START FROM 0
  FROM Employees
  WHERE EMPID = 7
  UNION ALL
  -- Recursive Member (RM)
  SELECT 
      m.empid,   -- these columns need to come from m
      m.empname, -- these columns need to come from m
      m.mgrid,   -- these columns need to come from m
      e.level+1                     -- <------------------- INCREMENT LVL
  FROM Employees AS m
    JOIN EmpCTE AS e                    -- <------------------- RECURSIVELY CALL EmpCTE
      ON e.mgrid = m.empid
)
SELECT * FROM EmpCTE;

empid       empname                   mgrid       level
----------- ------------------------- ----------- -----------
7           Robert                    3           0
3           Janet                     1           1
1           Nancy                     NULL        2