ComponentConfigurationID ComponentID NextComponentID
1 G001 J001
2 G003 J007
3 G010 J021
4 J001 B001
5 J001 B002
6 B001 B003
7 B003 B004
8 B002 B005
9 J007 B003
10 J007 B005
11 B005 B001
12 B005 B002
13 J021 B001
14 J021 B002
15 J021 B003
16 B001 B004
17 B001 B005 需求说明:(以B开头的ID可以挂在以J开头的ID下,也可以挂在以B开头的ID下)我现在只想取出J001下的所有子节点,这个语句应该怎么写,谢谢。
1 G001 J001
2 G003 J007
3 G010 J021
4 J001 B001
5 J001 B002
6 B001 B003
7 B003 B004
8 B002 B005
9 J007 B003
10 J007 B005
11 B005 B001
12 B005 B002
13 J021 B001
14 J021 B002
15 J021 B003
16 B001 B004
17 B001 B005 需求说明:(以B开头的ID可以挂在以J开头的ID下,也可以挂在以B开头的ID下)我现在只想取出J001下的所有子节点,这个语句应该怎么写,谢谢。
-----------------------------------
B开头的可以挂在自己的下面,那且不是成了死循环?
B001 B001
12 B005 B002 --------------------
表数据有问题,会死循环的
select NextComponentID from table where ComponentID='J001' union all select NextComponentID where ComponentID in( select NextComponentID from table where ComponentID='J001' ) t1union all select NextComponentID where ComponentID in( select NextComponentID where ComponentID in( select NextComponentID from table where ComponentID='J001' ) t21) t22
如果多个语句declare @t table(ComponentID varchar(10),visited int)
declare @nextfloor int
insert into @t select CoponentID,0 from table where ComponentID='J001'
select @nextfloor=count(*) from @t where visited=0
while(@nextfloor>0)
begin
insert into @t select CoponentID,2 from table where ComponentID in (select CoponentID from @t where visited=0 ) a
update @t set visited=1 where visited=0
update @t set visited=0 where visited=2
select @nextfloor=count(*) from @t where visited=0
end
select ComponetID from @t
http://blog.csdn.net/roy_88/archive/2008/01/15/2045842.aspx
http://blog.csdn.net/roy_88/archive/2006/12/24/1458449.aspx
declare @t table(ComponentID varchar(10),visited int) //建一个临时表,保存符合条件的所有ID,visited表示结点状态,0表示活结点,1表示死结点,2表示是刚插入的小孩结点
declare @nextfloor int //递归的下一层还有多少活结点(活结点即还可能生小孩的结点)
insert into @t select CoponentID,0 from table where ComponentID='J001' //初始化,第一层结点
select @nextfloor=count(*) from @t where visited=0//第一层结点应当都是可能生小孩的
while(@nextfloor>0)//循环
begin
insert into @t select CoponentID,2 from table where ComponentID in (select CoponentID from @t where visited=0 ) a//将临时表中所有活结点的小孩都插入临时表,
update @t set visited=1 where visited=0//将活结点变成死结点(已经将小孩取出,不能再取了)
update @t set visited=0 where visited=2//将小孩变成活结点
select @nextfloor=count(*) from @t where visited=0//选择可供使用的活结点个数,循环
end
select ComponetID from @t//取出所有记录..
B005是父结点但同时又是NextComponentID 子结点
B005下面又挂了一个B001。我现在只想要 J001-B005-B001的树状结构。
create procedure P_test
@vID varchar(10)
as
begin
declare @t table(ComponentID varchar(10),visited int)
declare @nextfloor int
insert into @t select CoponentID,0 from table1 where ComponentID=@vID
select @nextfloor=count(*) from @t where visited=0
while(@nextfloor>0)
begin
insert into @t select distinct CoponentID,2 from table1 where ComponentID in (select CoponentID from @t where visited=0 )
and ComponentID not in (select ComponentID from @t)
update @t set visited=1 where visited=0
update @t set visited=0 where visited=2
select @nextfloor=count(*) from @t where visited=0
end
select ComponetID from @t
end