递归CTE

WITH EmployeeCTE AS
(
    
    
    --anchor 
     Select EmployeeId, EmployeeName, ManagerID
     From Employees
     Where EmployeeId =7
    
     UNION ALL
    
     Select Employees.EmployeeId , Employees.EmployeeName,
             Employees.ManagerID
     From Employees
     JOIN EmployeeCTE
     ON Employees.EmployeeId = EmployeeCTE.ManagerID
)

##this cte will give me all the people up in the ladder
select * from EmployeeCTE;


#### DO A SELF JOIN NOW OF THIS CTE because we want names in place of ids

on CTE1.employeeid=CTE2managerid


Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName
From EmployeeCTE E1
LEFT Join EmployeeCTE E2
ON E1.ManagerID = E2.EmployeeId

EXAMPLE
https://myadventuresincoding.wordpress.com/2014/05/02/sql-server-simple-recursive-query-example/