create table t
(id int,nextnode int)insert t
select 1,3 union all
select 2,100 union all
select 3,5 union all
select 4,7 union all
select 5,0
gocreate function f_go(@col int)
returns @t table(id int,nextnode int,level int)
as
begin
declare @i int
set @i=1
insert @t select *,@i from t where id=@col
while @@rowcount<>0
begin
set @i=@i+1
insert @t select a.*,@i from t a,@t b
where b.nextnode=a.id and b.level=@i-1
end
return
end
goselect id,nextnode from dbo.f_go(1)drop function f_go
drop table tid nextnode
----------- -----------
1 3
3 5
5 0(所影响的行数为 3 行)
(id int,nextnode int)insert t
select 1,3 union all
select 2,100 union all
select 3,5 union all
select 4,7 union all
select 5,0
gocreate function f_go(@col int)
returns @t table(id int,nextnode int,level int)
as
begin
declare @i int
set @i=1
insert @t select *,@i from t where id=@col
while @@rowcount<>0
begin
set @i=@i+1
insert @t select a.*,@i from t a,@t b
where b.nextnode=a.id and b.level=@i-1
end
return
end
goselect id,nextnode from dbo.f_go(1)drop function f_go
drop table tid nextnode
----------- -----------
1 3
3 5
5 0(所影响的行数为 3 行)
declare @I int
declare @t1 table(id int,nextnode int)
declare @t2 table(id int,nextnode int)
declare @t3 table(id int,nextnode int)
set @ID=1
insert @t1 select * from A where id=@ID
insert @t2 select * from A where id=@ID
set @I=@@rowcount
while @I>0
begin
insert @t1 select * from A where id in (select nextnode from @t2)
set @I=@@rowcount
insert @t3 select * from A where id in (select nextnode from @t2)
delete from @t2
insert @t2 select * from @t3
delete from @t3
end
select * from @t1
create function f_go(@ID int)
returns @t1 table(id int,nextnode int)
as
begin
declare @I int
declare @t2 table(id int,nextnode int)
declare @t3 table(id int,nextnode int)
insert @t1 select * from A where id=@ID
insert @t2 select * from A where id=@ID
set @I=@@rowcount
while @I>0
begin
insert @t1 select * from A where id in (select nextnode from @t2)
set @I=@@rowcount
insert @t3 select * from A where id in (select nextnode from @t2)
delete from @t2
insert @t2 select * from @t3
delete from @t3
end
return
endselect * from dbo.f_go(1)