SQL Server - 재귀적 공통 테이블 식 (Recursive CTE)

재귀적 CTE 예

왼쪽)직원 관계 테이블, 오른쪽)우리가 뽑아내야할 테이블

--A simple self join
Select Employee.Name as [Employee Name],
IsNUll(Manager.Name, 'Super Boss') as [Manager Name]
from tblEmployee Employee
left join tblEmployee Manager
on Employee.ManagerId = Manager.EmployeeId

계층을 나누면 이렇게 되어 있음.

With
	EmployeesCTE (EmployeeId, Name, ManagerId, [Level])
    as
    (
    	Select EmployeedId, Name, ManagerId, 1
        from tblEmployee
        where ManagerId is null
        
        union all
        
        Select tblEmployee.EmployeedId, tblEmployee.Name, 
        tblEmployee.ManagerId, EmployeesCTE.[Level] + 1
        from tblEmployee
        join EmployeesCTE
        on tblEmployee.ManagerID = EmployeesCTE.EmployeeId
     )
Select EmpCTE.Name as Employee, Isnull(MgrCTE.Name, 'Super Boss') as Manager, EmpCTE.[Level]
from EmployeesCTE EmpCTE
left join EmployeesCTE MgrCTE
on EmpCTE.ManagerId = MgrCTE.EmployeeId

 

계속 스스로를 참조하는 재귀형 CTE