SQL SERVER 递归 CTE

 

CREATE TABLE Employee_Tree (Employee_NM nvarchar(50), Employee_ID int PRIMARY KEY, ReportsTo int)
--insert some data, build a reporting tree
INSERT INTO Employee_Tree VALUES('Richard', 1, NULL)
INSERT INTO Employee_Tree VALUES('Stephen', 2, 1)
INSERT INTO Employee_Tree VALUES('Clemens', 3, 2)
INSERT INTO Employee_Tree VALUES('Malek', 4, 2)
INSERT INTO Employee_Tree VALUES('Goksin', 5, 4)
INSERT INTO Employee_Tree VALUES('Kimberly', 6, 1)
INSERT INTO Employee_Tree VALUES('Ramesh', 7, 5)
--
with MyCTE
as
( select Employee_ID, Employee_NM, -1 as ReportsTo, 0 as SubLevel
 from Employee_Tree where ReportsTo = 2 --root node  --这个值就是自己要传入的值
 union all
 select e.Employee_ID, e.Employee_NM, e.ReportsTo, SubLevel +1
 from Employee_Tree e, MyCTE where e.ReportsTo = MyCTE.Employee_ID

) --select * from MyCTE
select * from MyCTE