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
不知道是不是搂主想要的 第二个游标可以不用而用临时表,把所有child插入临时表最后组合得到@ids create table Tree(child varchar(20),parent varchar(20)) insert into 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' alter proc GetAllChild(@InputNode varchar(20)) as declare @child varchar(50) declare @child1 varchar(50) declare @ids varchar(50)--根据情况定义大小 set @ids='' declare child cursor for select child from Tree where parent=@InputNode open child fetch next from child into @child while @@fetch_status=0 begin if len(@ids)=0 select @ids=''''+@child+'''' else select @ids=@ids+','''+@child+'''' --定义游标得到子接点的字接点 declare child1 cursor for select child from Tree where parent=@child open child1 fetch next from child1 into @child1 while @@fetch_status=0 begin select @ids=@ids+','''+@child1+'''' fetch next from child1 into @child1 end close child1 deallocate child1 fetch next from child into @child end close child deallocate child print @ids--exec GetAllChild '000'
wgsasd311(自强不息) 你的存储过程会返回所有的子节点,而不是某一个父节点的子节点
wgsasd311(自强不息)sorry,看错了,你的答案是正确的,谢谢
select child from tree where parent=@inputnode 那这句是最简单的了
發個遞歸的: create function GetChild (@note varchar(20)) returns varchar(1000) AS begin declare @strChild varchar(1000),@strReturn varchar(1000) Set @strChild='' Set @strReturn='' Declare Child_cursor cursor for select child from Tree where parent=@note open Child_cursor fetch next from Child_cursor into @strChild while @@FETCH_STATUS=0 begin
SET @strReturn=@strReturn+@strChild+';'+dbo.GetChild(@strChild) fetch next from Child_cursor into @strChild end close Child_cursor deallocate Child_cursor return @strReturn endselect dbo.GetChild('001')
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
第二个游标可以不用而用临时表,把所有child插入临时表最后组合得到@ids
create table Tree(child varchar(20),parent varchar(20))
insert into 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'
alter proc GetAllChild(@InputNode varchar(20))
as
declare @child varchar(50)
declare @child1 varchar(50)
declare @ids varchar(50)--根据情况定义大小
set @ids=''
declare child cursor for select child from Tree where parent=@InputNode
open child
fetch next from child into @child
while @@fetch_status=0
begin
if len(@ids)=0
select @ids=''''+@child+''''
else
select @ids=@ids+','''+@child+''''
--定义游标得到子接点的字接点
declare child1 cursor for select child from Tree where parent=@child
open child1
fetch next from child1 into @child1
while @@fetch_status=0
begin
select @ids=@ids+','''+@child1+''''
fetch next from child1 into @child1
end
close child1
deallocate child1
fetch next from child into @child
end
close child
deallocate child
print @ids--exec GetAllChild '000'
你的存储过程会返回所有的子节点,而不是某一个父节点的子节点
那这句是最简单的了
create function GetChild
(@note varchar(20))
returns varchar(1000)
AS
begin
declare @strChild varchar(1000),@strReturn varchar(1000)
Set @strChild=''
Set @strReturn=''
Declare Child_cursor cursor for select child from Tree where parent=@note
open Child_cursor
fetch next from Child_cursor into @strChild
while @@FETCH_STATUS=0
begin
SET @strReturn=@strReturn+@strChild+';'+dbo.GetChild(@strChild)
fetch next from Child_cursor into @strChild
end
close Child_cursor
deallocate Child_cursor
return @strReturn
endselect dbo.GetChild('001')