递归例子如下:
逐级查询:
with cte as
(
select EmployeeNumber,AssociateCHName,SupervisorNumber,Supervisor from EmployeeApprovalDOA
where EmployeeNumber='J0101134'
union all
select b.EmployeeNumber,b.AssociateCHName,b.SupervisorNumber,b.Supervisor from cte c inner join EmployeeApprovalDOA b
on c.SupervisorNumber=b.EmployeeNumber
)
select top(10) * from cte
--where EmployeeNumber<>'J0101036'
查询此人下级:
with cte as
(
select EmployeeNumber,AssociateCHName,SupervisorNumber,Supervisor from EmployeeApprovalDOA
where EmployeeNumber='J0101201'
union all
select b.EmployeeNumber,b.AssociateCHName,b.SupervisorNumber,b.Supervisor from cte c inner join EmployeeApprovalDOA b
on b.SupervisorNumber=c.EmployeeNumber
)
select top(10) * from cte