找出所有子结点。

[chengangcsdn]

表如下：
getchild:两个字段。myself表示自己,father表示它的父目录。

myself father
a NULL
b a
b1 a
b2 a
c b
c1 b
d c
求一语句:得到一个结点的所有子结点
如：
输入a:
则得到：
b b1 b2 c c1 d

[chengangcsdn]

谢谢各位!

[chengangcsdn]

怎么没有人顶啊。
UP

[chengangcsdn]

我觉得这个查询语句还是很有意思的啊。
看了没有回答也顶顶啊

[chengangcsdn]

郁闷。
顶顶啊

[xianglong]

select myself from getchild a left join getchild b on a.myself<>b.father

[xianglong]

我在赶趟儿，随便写了下，效率不高。

[didoleo]

create table getchild
(
myself varchar(100),
father varchar(100)
)

insert into getchild
select 'a' , NULL union all
select 'b' , 'a' union all
select 'b1' , 'a' union all
select 'b2' , 'a' union all
select 'c' , 'b' union all
select 'c1' , 'b' union all
select 'd' , 'c'


create function dbo.getSubtreeInfo
(
@father as varchar(100)
)
returns @treeinfo table
(
myself varchar(100),
father varchar(100),
level int
)
begin
declare @level as int
select @level=0
insert into @treeinfo
select myself,father,@level from getchild where myself=@father
while @@rowcount>0
begin
set @level=@level+1
insert into @treeinfo
select e.myself,e.father,@level from getchild as e join @treeinfo as t
on e.father=t.myself and t.level=@level-1
end
return
end


declare @s varchar(1000)
set @s=' '
select @s=@s+ myself+',' from dbo.getsubtreeinfo('a') where myself<>'a'
print left(@s,len(@s)-1)
-----------------------------------
b,b1,b2,c,c1,d

[chengangcsdn]

结果并不需要用字符串连起来
只要显示相应的记录就行。
也就是输入a
将他所有的子结点记录显示出来
有没有简单一点的。

[xianglong]

我错了。

[didoleo]

select myself,father from dbo.getsubtreeinfo('a') where myself<>'a'
-------------------------------------------------------------------
myself father
b a
b1 a
b2 a
c b
c1 b
d c


（所影响的行数为 6 行）

[FlyNesta]

CREATE FUNCTION GetMyChild
(@parent varchar(1000))
RETURNS varchar(1000)
AS
begin

declare @ret varchar(5000)
set @ret = ''

declare child_cursor cursor for
select myself from getchild where father = @parent

open child_cursor

declare @nextnode varchar(1000)
declare @temp varchar(1000)
set @temp = ''
FETCH NEXT FROM child_cursor into @nextnode

WHILE @@FETCH_STATUS = 0
BEGIN
if(len(@ret) = 0)
set @ret = @nextnode
else
set @ret = @ret + ',' + @nextnode

set @temp = dbo.GetMyChild(@nextnode)
if(len(@temp) > 0)
set @ret = @ret + ',' + dbo.GetMyChild(@nextnode)

FETCH NEXT FROM child_cursor into @nextnode

END

CLOSE child_cursor
DEALLOCATE child_cursor

return @ret
end

--------------------测试
declare @ss varchar(4000)
set @ss = 'a'
set @ss = dbo.GetMyChild(@ss)
print @ss