表名 a
declare @a int
set @a=2
select * into #ab from a where 1=0
while(exists(select * from a where parentid = @a))
begin
select * from #ab union select * from a where parentid =@a
select @a=id from a where parentid=@a
end
declare @a int
set @a=2
select * into #ab from a where 1=0
while(exists(select * from a where parentid = @a))
begin
select * from #ab union select * from a where parentid =@a
select @a=id from a where parentid=@a
end
你只能得到第二级的。能否在改一下,得到全部子节点?
是干什么的呀
declare @Test table ( [id] int primary key , content varchar(10), ParentId int) insert into @Table values ( 1, 'a', 0 )
insert into @Table values ( 2, 'b', 1 )
insert into @Table values ( 3, 'c', 1 )
insert into @Table values ( 4, 'd', 2 )
insert into @Table values ( 5, 'e', 4 )
insert into @Table values ( 8, 'e', 5 ) insert into @Test select * from @Table where [id] = 2 while exists ( select * from @Table A
inner join @Test B
on A.ParentId = B.[Id]
where A.[Id] not in ( select [id] from @Test ) )
begin
insert into @Test
select A.* from @Table A
inner join @Test B
on A.ParentId = B.[Id]
where A.[Id] not in ( select [id] from @Test )
end
select * from @Test
declare @Test table ( [id] int primary key , content varchar(10), ParentId int) insert into @Table values ( 1, 'a', 0 )
insert into @Table values ( 2, 'b', 1 )
insert into @Table values ( 3, 'c', 1 )
insert into @Table values ( 4, 'd', 2 )
insert into @Table values ( 5, 'e', 4 )
insert into @Table values ( 8, 'e', 5 ) insert into @Test select * from @Table where [id] = 2 while ( @@RowCount <> 0 )
begin
insert into @Test
select A.* from @Table A
inner join @Test B
on A.ParentId = B.[Id]
where A.[Id] not in ( select [id] from @Test )
end
select * from @Test