SQL Server CTE 自下而上遞歸與 Where 子句

我有一個包含 EmployeeId、ManagerId 和 Name 欄位的員工表。

目標是進行遞歸，從員工獲取所有行到最高管理者（ManagerId 為空）。

我發現這個連結有助於獲取程式碼的基礎，但我無法讓它適用於我的案例

DECLARE @EmployeeTable table ([EmployeeId] int, [name] varchar(10), [managerId] int)
INSERT @EmployeeTable VALUES (1,'Jerome', NULL )  -- tree is as follows:
INSERT @EmployeeTable VALUES (2,'Joe'   ,1)     --                      1-Jerome
INSERT @EmployeeTable VALUES (3,'Paul'  ,2)     --                     /        \
INSERT @EmployeeTable VALUES (4,'Jack'  ,3)     --              2-Joe               9-Bill
INSERT @EmployeeTable VALUES (5,'Daniel',3)     --            /       \                  \
INSERT @EmployeeTable VALUES (6,'David' ,2)     --     3-Paul          6-David       10-Sam
INSERT @EmployeeTable VALUES (7,'Ian'   ,6)     --    /      \            /    \
INSERT @EmployeeTable VALUES (8,'Helen' ,6)     -- 4-Jack  5-Daniel   7-Ian    8-Helen
INSERT @EmployeeTable VALUES (9,'Bill ' ,1)     --
INSERT @EmployeeTable VALUES (10,'Sam'  ,9)     --

DECLARE @employeeId int = 3

;WITH StaffTree AS
(
   SELECT 
       c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
       FROM @EmployeeTable c
       LEFT OUTER JOIN @EmployeeTable  cc ON c.managerId=cc.EmployeeId
       WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND     c.managerId IS NULL)
   UNION ALL
       SELECT 
           s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
       FROM StaffTree t
           INNER JOIN @EmployeeTable s ON t.[EmployeeId]=s.managerId
       WHERE s.managerId=@employeeId OR @employeeId IS NULL OR t.Level>1
)
SELECT * FROM StaffTree

如果您選擇員工 3 層次結構，則結果應為：

EmployeeId | Name    | ManagerId 
1          | Jerome  | NULL
2          | Joe     | 1
3          | Paul    | 2

在遞歸部分交換內部連接中的列是解決此問題的一種方法。

連接列從更改t.[EmployeeId]=s.managerId為s.[EmployeeId]=t.managerId

我刪除了一些看起來不需要的部分。

DECLARE @employeeId int = 3

;WITH StaffTree AS
(
   SELECT 
       c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
       FROM @EmployeeTable c
       WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND     c.managerId IS NULL)
   UNION ALL
       SELECT 
           s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
       FROM StaffTree t
       INNER JOIN @EmployeeTable s ON s.[EmployeeId]=t.managerId
)
SELECT EmployeeId,
      name,
      managerId 
FROM StaffTree
ORDER BY managerId asc;

結果

EmployeeId  name    managerId
1           Jerome  NULL
2           Joe     1
3           Paul    2

引用自：https://dba.stackexchange.com/questions/229515