根据一个节点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.数据节点是不定的,可多级
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.数据节点是不定的,可多级
--> 测试数据:@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 行受影响)
*/
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