T-Sql 递归查询

使用CTE查询递归层级：(Sql CTE Recursion)

WITH CTE_MA AS 
(
    SELECT 0 AS Tier, EmployeeID, ManagerID, FirstName, LastName
    FROM dbo.MyEmployees
    WHERE EmployeeID = 1
    UNION ALL
    SELECT a.Tier+1 AS Tier, e.EmployeeID, e.ManagerID, e.FirstName, e.LastName
    FROM dbo.MyEmployees e
    INNER JOIN CTE_MA a ON e.ManagerID = a.EmployeeID
)
SELECT * FROM CTE_MA

 

Tier        EmployeeID ManagerID   FirstName                      LastName
----------- ---------- ----------- ------------------------------ ----------------------------------------
0           1          NULL        Ken                            Sánchez
1           273        1           Brian                          Welcker
2           16         273         David                          Bradley
2           274        273         Stephen                        Jiang
2           285        273         Syed                           Abbas
3           286        285         Lynn                           Tsoflias
3           275        274         Michael                        Blythe
3           276        274         Linda                          Mitchell
3           23         16          Mary                           Gibson