CTE 递归举例
1![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
--递归CTE
3![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
Use tempdb
5
Go
6![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
Create Table Dept(
8
Id Int primary key,
9
Parent_Id int,
10
[name] nvarchar(50)
11
)
12![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
Insert Dept
14
Select 0,0,N'<All>' Union All
15
Select 1,0,N'财务部' Union All
16
Select 2,0,N'行政部' Union All
17
Select 3,0,N'业务部' Union All
18
Select 4,3,N'软件开发' Union All
19
Select 5,3,N'软件测试'
20
Go
21![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
--查询所有部门
23
Declare @DeptName nvarchar(50)
24
Set @DeptName = '业务部'
25![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
;With
27
CTE_Depts as
28
(
29
--定位点成员
30
Select * From Dept
31
Where [name]=@DeptName
32
Union All
33
Select A.*
34
From Dept A,CTE_Depts B
35
Where A.Parent_Id = B.Id
36
)
37![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
Select * From CTE_Depts
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
欢迎加群互相学习,共同进步。QQ群:iOS: 58099570 | Android: 572064792 | Nodejs:329118122
做人要厚道,转载请注明出处!