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