Create Table #YourTable ([Employee] varchar(50),[Manager] varchar(50))
Insert Into #YourTable Values
('Jack',null)
,('Luna','Jack')
,('Japan','Jack')
,('Alice','Luna')
,('Alex','Luna')
,('Jessica','Alex')
Select *
,Lvl=1
,Path=convert(varchar(500),Employee)
Into #TempBld
From #YourTable
Where Manager is null
Declare @Cnt int=1
While @Cnt<=30 -- Set Max Level -- You can be a little generous here.
Begin
Insert Into #TempBld
Select A.*
,Lvl =B.Lvl+1
,Path=B.Path+' - '+A.Employee
From #YourTable A
Join #TempBld B on (B.Lvl=@Cnt and A.Manager=B.Employee)
Set @Cnt=@Cnt+1
End
Select * from #TempBld Order by Path
结果
Lvl Employee Manager Path
1 Jack NULL Jack
2 Japan Jack Jack - Japan
2 Luna Jack Jack - Luna
3 Alex Luna Jack - Luna - Alex
4 Jessica Alex Jack - Luna - Alex - Jessica
3 Alice Luna Jack - Luna - Alice
Recursive CTEs are great to a point. However, if you are looking to build a large hierarchy (like 200K+), there is NO shame in using TEMP tables.
Here is a stripped down/modified version I've used for my LARGE and SLOW MOVING hierarchies.
Example
Create Table #YourTable ([Employee] varchar(50),[Manager] varchar(50))
Insert Into #YourTable Values
('Jack',null)
,('Luna','Jack')
,('Japan','Jack')
,('Alice','Luna')
,('Alex','Luna')
,('Jessica','Alex')
Select *
,Lvl=1
,Path=convert(varchar(500),Employee)
Into #TempBld
From #YourTable
Where Manager is null
Declare @Cnt int=1
While @Cnt<=30 -- Set Max Level -- You can be a little generous here.
Begin
Insert Into #TempBld
Select A.*
,Lvl =B.Lvl+1
,Path=B.Path+' - '+A.Employee
From #YourTable A
Join #TempBld B on (B.Lvl=@Cnt and A.Manager=B.Employee)
Set @Cnt=@Cnt+1
End
Select * from #TempBld Order by Path
Results
Lvl Employee Manager Path
1 Jack NULL Jack
2 Japan Jack Jack - Japan
2 Luna Jack Jack - Luna
3 Alex Luna Jack - Luna - Alex
4 Jessica Alex Jack - Luna - Alex - Jessica
3 Alice Luna Jack - Luna - Alice
发布评论
评论（1）
递归CTE非常好。但是，如果您想构建一个较大的层次结构（例如200k+），则使用temp表中的没有羞耻感。
这是我用于大型和缓慢移动的层次结构的脱衣/修改版本。
示例
结果
Recursive CTEs are great to a point. However, if you are looking to build a large hierarchy (like 200K+), there is NO shame in using TEMP tables.
Here is a stripped down/modified version I've used for my LARGE and SLOW MOVING hierarchies.
Example
Results