根据一个节点ID获取该节点下的所有子节点^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

[cefriend]

根据一个节点ID获取该节点下的所有子节点

如表A
id name fatherID
1 a 0
2 b 1
3 c 2
4 d 3
5 e 4
6 f 5
7 g 6

我传一个参数id = 3 得到数据表
id name fatherid
3 c 2
4 d 3
5 e 4
6 f 5
7 g 6

请问如何写SQL.数据节点是不定的,可多级

[dobear_0922]

SQL2000的话需要要自定义函数，SQL2005可以用CET递归查询

[dobear_0922]

--> By dobear_0922(小熊) 2008-11-06 17:25:10
--> 测试数据：@t
declare @t table([id] int,[name] varchar(1),[fatherID] int)
insert @t
select 1,'a',0 union all
select 2,'b',1 union all
select 3,'c',2 union all
select 4,'d',3 union all
select 5,'e',4 union all
select 6,'f',5 union all
select 7,'g',6

--select * from @t
declare @id int
set @id=3
;with nt as
(
	select * from @t where id=@id
	union all 
	select t.* from @t t join nt on t.[fatherID]=nt.[id]
)
select * from nt
/*
id          name fatherID
----------- ---- -----------
3           c    2
4           d    3
5           e    4
6           f    5
7           g    6

(5 行受影响)
*/

[utpcb]

CET递归查

[wzy_love_sly]

create table tb(id int,name varchar(50),fatherid int)
insert into tb select 1,'a',0
insert into tb select 2,'b',1
insert into tb select 3,'c',2
insert into tb select 4,'d',3
insert into tb select 5,'e',4
insert into tb select 6,'f',5
insert into tb select 7,'g',6
go
declare @id int
set @id=3;
with dom as
(
select id,name,fatherid from tb where id=@id
union all
select a.id,a.name,a.fatherid from tb  a join dom b on a.fatherid=b.id
)
select * from dom 

id name fatherid
3 c 2
4 d 3
5 e 4
6 f 5
7 g 6