SQL SERVER 递归 CTE

最新推荐文章于 2025-03-11 13:25:13 发布

原创最新推荐文章于 2025-03-11 13:25:13 发布 · 394 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #tree #insert #build #table #null

SQL 专栏收录该内容

47 篇文章

订阅专栏

本文详细介绍了如何使用SQL语句创建一个员工报告树，并通过递归查询构建子节点列表，实现组织结构可视化。

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