这个SQL语句要怎么写？？

[simon__sun]

有一张部门表：
部门编号 部门名称 上级部门编号
1 tes1 0
2 tes2 0
3 tes3 2
4 tes4 1
5 tes5 3
6 tes6 3
7 tes7 4

现在要通过一条sql语句列出所有该部门及子部门，子部门层级不限。 如部门编是2 要怎么写得到下面的内容，

2 tes2 0
3 tes3 2
5 tes5 3
6 tes6 3

[duanzhi1984]

SQL2005 with cte 递归

[hao1hao2hao3]

with cte as 
(
	select * from tab where 上级部门编号 = 0
	union all
	select * from tab join cte on tab.上级部门编号= cte.部门编号
)
select * from cte

[SQLCenter]

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(部门编号 int, 部门名称 varchar(8), 上级部门编号 int)
insert into #
select 1, 'tes1', 0 union all
select 2, 'tes2', 0 union all
select 3, 'tes3', 2 union all
select 4, 'tes4', 1 union all
select 5, 'tes5', 3 union all
select 6, 'tes6', 3 union all
select 7, 'tes7', 4

declare @dept int
set @dept = 2;

with t as
(
	select * from # where 部门编号 = @dept
	union all
	select #.* from # join t on #.上级部门编号 = t.部门编号
)
select * from t

/*
部门编号    部门名称 上级部门编号
----------- -------- -----------
2           tes2     0
3           tes3     2
5           tes5     3
6           tes6     3
*/

[rucypli]

with cte as(
select a from test A where b=2
union all
select B.a from cte A ,test B where B.b=A.a
)
select * from cte

[duanzhi1984]

create table #t(部门编号 int,部门名称 varchar(10), 上级部门编号 int)

insert into #t

select  1,'tes1',0 union all
select 2,'tes2',0 union all
select 3,'tes3',2 union all
select 4,'tes4',1union all
select 5,'tes5',3union all
select 6,'tes6',3union all
select 7,'tes7', 4

with c (部门编号,部门名称,上级部门编号) as 
(
select 部门编号,部门名称,上级部门编号 from #t where 部门编号=2
union all
select a.部门编号,a.部门名称,a.上级部门编号 from #t a join  c on a.上级部门编号=c.部门编号 
)
select *from c

2	tes2	0
3	tes3	2
5	tes5	3
6	tes6	3

[hao1hao2hao3]

[Quote=引用 3 楼 hao1hao2hao3 的回复:]
SQL code

with cte as
(
select * from tab where 上级部门编号 = 0
union all
select * from tab join cte on tab.上级部门编号= cte.部门编号
)
select * from cte
[/Quote]

不好意思，应该是

with cte as 
(
    select * from tab where 上级部门编号 = 0
    union all
    select tab.* from tab join cte on tab.上级部门编号= cte.部门编号
)
select * from cte

[hao1hao2hao3]

好像写反了，

with cte as
(
select * from tab where 部门编号 = 2
union all
select tab.* from tab join cte on tab.上级部门编号= cte.部门编号
)
select * from cte

[fly_Apple10]

http://blog.csdn.net/fly_Apple10/archive/2010/08/08/5796667.aspx



类似的
今天上午写的

[leelin2010]

with cte as
(
select * from tableName where 部门编号=2
union all
select * from tableName a
join cte t on a.上级部门编号=t.部门编号
)
select * from cte

[aspNet123Xu]

DECLARE @depno int;
SET @depno=3;

with cte(部门编号,部门名称,上级部门编号,lvl)
AS
(
	SELECT 部门编号,部门名称,上级部门编号,0 FROM # WHERE 部门编号=@depno
	UNION ALL
	SELECT tb.部门编号,tb.部门名称,tb.上级部门编号,lvl+1 FROM # tb JOIN cte ON tb.上级部门编号=cte.部门编号
)
select 部门编号,部门名称,上级部门编号,lvl '部门阶层' from cte

3 tes3 2 0
5 tes5 3 1
6 tes6 3 1

[永生天地]

http://blog.csdn.net/xys_777/archive/2010/06/15/5672481.aspx