create table tree(child varchar(20),parent varchar(20)) insert tree select '001','000' union all select '002','001' union all select '003','001' union all select '004','002' union all select '005','002' union all select '006','003' go create proc p(@inputnode varchar(20)) as declare @i int set @i=0 declare @re table(child varchar(20),parent varchar(20),level int) insert into @re select child,parent,@i from tree where child=@inputnode while @@rowcount>0 begin set @i=@i+1 insert into @re select a.child,a.parent,@i from tree a,@re b where a.parent=b.child and b.level=@i-1 end select child from @re go exec p '001' drop table tree drop proc p
create table tree(parent int null,child int null) insert into tree(child,parent) values(2,1) insert into tree(child,parent) values(3,2) insert into tree(child,parent) values(4,3) insert into tree(child,parent) values(5,2) insert into tree(child,parent) values(8,1) insert into tree(child,parent) values(6,4) go --以下是获取字节接点的函数 create function udf_getchild(@dot_id int) returns @child_list table(parent int null,child int null) as begin declare @temp_table table(id int identity(1,1),parent int null,child int null) declare @max_id int, @loop_row_id int, @temp_dot_id int insert into @child_list(parent,child) select parent,child from tree where parent=@dot_id insert into @temp_table(parent,child) select parent,child from tree where parent=@dot_id select @max_id=max(id),@loop_row_id=min(id)from @temp_table if @loop_row_id=0 or @loop_row_id is null set @loop_row_id=1 while @max_id>=@loop_row_id begin select @temp_dot_id=child from @temp_table where id=@loop_row_id insert into @child_list(parent,child) select * from dbo.udf_getchild(@temp_dot_id) set @loop_row_id=@loop_row_id+1 end return endselect * from dbo.udf_getchild(2)
create table tree(child varchar(20),parent varchar(20))
insert tree
select '001','000' union all
select '002','001' union all
select '003','001' union all
select '004','002' union all
select '005','002' union all
select '006','003'
go
create proc p(@inputnode varchar(20))
as
declare @i int
set @i=0
declare @re table(child varchar(20),parent varchar(20),level int)
insert into @re
select child,parent,@i from tree where child=@inputnode
while @@rowcount>0
begin
set @i=@i+1
insert into @re
select a.child,a.parent,@i from tree a,@re b where a.parent=b.child and b.level=@i-1
end
select child from @re
go
exec p '001'
drop table tree
drop proc p
insert into tree(child,parent) values(2,1)
insert into tree(child,parent) values(3,2)
insert into tree(child,parent) values(4,3)
insert into tree(child,parent) values(5,2)
insert into tree(child,parent) values(8,1)
insert into tree(child,parent) values(6,4)
go
--以下是获取字节接点的函数
create function udf_getchild(@dot_id int)
returns @child_list table(parent int null,child int null)
as
begin
declare @temp_table table(id int identity(1,1),parent int null,child int null)
declare @max_id int,
@loop_row_id int,
@temp_dot_id int
insert into @child_list(parent,child) select parent,child from tree where parent=@dot_id
insert into @temp_table(parent,child) select parent,child from tree where parent=@dot_id
select @max_id=max(id),@loop_row_id=min(id)from @temp_table
if @loop_row_id=0 or @loop_row_id is null set @loop_row_id=1
while @max_id>=@loop_row_id
begin
select @temp_dot_id=child
from @temp_table where id=@loop_row_id
insert into @child_list(parent,child) select * from dbo.udf_getchild(@temp_dot_id)
set @loop_row_id=@loop_row_id+1
end
return
endselect * from dbo.udf_getchild(2)