1
2
--遞歸cte
3
4
use tempdb
5
go
6
7
create table dept(
8
id int primary key,
9
parent_id int,
10
[name] nvarchar(50)
11
)
12
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
21
22
--查詢所有部門
23
declare @deptname nvarchar(50)
24
set @deptname = '業務部'
25
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
38
select * from cte_depts
歡迎加群互相學習，共同進步。qq群：ios: 58099570 | android: 330987132 | go：217696290 | python：336880185 | 做人要厚道，轉載請注明出處！http://www.cnblogs.com/sunshine-anycall/archive/2009/03/22/1418756.html