有一个表，结构为Tree(Child,Parent),写一个存储过程，[email protected]
------解决方案--------------------
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 [email protected]
while @@rowcount> 0
begin
set @[email protected]+1
insert into @re
select a.child,a.parent,@i from tree a,@re b where a.parent=b.child and [email protected]
end
select child from @re
go
exec p '001 '
drop table tree
drop proc p