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

 1 WITH CTE_MA AS  2 ( 3     SELECT 0 AS Tier, EmployeeID, ManagerID, FirstName, LastName 4     FROM dbo.MyEmployees 5     WHERE EmployeeID = 1 6     UNION ALL 7     SELECT a.Tier+1 AS Tier, e.EmployeeID, e.ManagerID, e.FirstName, e.LastName 8     FROM dbo.MyEmployees e 9     INNER JOIN CTE_MA a ON e.ManagerID = a.EmployeeID10 )11 SELECT * FROM CTE_MA

Tier        EmployeeID ManagerID   FirstName                      LastName----------- ---------- ----------- ------------------------------ ----------------------------------------0           1          NULL        Ken                            Sánchez1           273        1           Brian                          Welcker2           16         273         David                          Bradley2           274        273         Stephen                        Jiang2           285        273         Syed                           Abbas3           286        285         Lynn                           Tsoflias3           275        274         Michael                        Blythe3           276        274         Linda                          Mitchell3           23         16          Mary                           Gibson

T-Sql 递归查询