高手请进, 求解一sql

[goes_kad]

table:
Parent Child
1 2
2 3
3 4
6 7
7 8
8 9

求如何用一句sql查找出指定parent的所有child id?
如指定parent为1, 则child有: 2,3,4

[billxia]

没有什么特殊的办法解决这个递归问题，因为递归深度不确定。
所以当初就不应该如此创建表，应该在id中包含所有上级的编码在内，那样就非常方便了。除非递归深度非常深，已经超过10层，那就没有什么好办法了。

[willdavis]

有点麻烦。关注

[paoluo]

你需要怎樣的結果？？

是這樣

child
2
3
4

紀錄集的形式

還是2,3,4這樣的（一個字符串）？？

[撸起柚子的秋风]

regions 表的结构

NodeID int 4
ParentID int 4

CREATE PROCEDURE Regions_Delete
@ParentID int
AS

create table #temp
(nodeid int )
insert into #temp select nodeid from regions where nodeid=@ParentID
select * into #temp2 from #temp
select * into #temp3 from #temp where 1!=1
while (1>0)
begin
insert into #temp3 select nodeid from regions where parentid in (select nodeid from #temp2)
if @@rowcount=0
break
insert into #temp select * from #temp3

truncate table #temp2
insert into #temp2 select * from #temp3
truncate table #temp3
end
delete from regions where nodeid in (select nodeid from #temp)

GO

[撸起柚子的秋风]

上面是一个存储过程
我目前只会这么处理了 ， 希望有更好的解决办法

[rivery]

--测试表及数据
create table ttest(parent int,child int)
go
insert into ttest(parent,child)
select 1,2 union select 2,3 union select 3,4 union select 6,7 union select 7,8 union select 8,9
go

--辅助函数
create function f_getchildid(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select child,@l from ttest where parent=@id
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.child,@l from ttest a join @re b on a.parent=b.id where b.level=@l-1
end
delete @re where id=@id
return
end
go
--执行
select id from dbo.f_getchildid(1)
--删除
drop table ttest
drop function dbo.f_getchildid
go

/*结果
2
3
4


*/

[paoluo]

--建立測試環境
Create Table Tree
(Parent Int,
Child Int)
--插入數據
Insert Tree Select 1, 2
Union All Select 2, 3
Union All Select 3, 4
Union All Select 6, 7
Union All Select 7, 8
Union All Select 8, 9
Go
--建立函數
Create Function GetChild(@Parent Int)
Returns @Child Table(Parent Int,Child Int)
As
Begin
Insert @Child Select * From Tree Where Parent=@Parent
While @@ROWCOUNT>0
Insert @Child Select B.* From @Child A Inner Join Tree B On A.Child=B.Parent Where B.Parent Not In (Select Distinct Parent From @Child)
Return
End
GO
--測試
Select Child From dbo.GetChild(1)
Select Child From dbo.GetChild(6)
Select Child From dbo.GetChild(2)
GO
--刪除測試環境
Drop Table Tree
Drop Function GetChild
Go
--結果
/*
Child
2
3
4

Child
7
8
9


Child
3
4
*/

[rivery]

--字符串结果(可以直接在函数内返回字符串，但建议函数返回表，以做更多的用途，可在外部组织得到字符串)
declare @s varchar(1000)
select @s=''
select @s=@s+','+convert(varchar,id) from dbo.f_getchildid(1)
select stuff(@s,1,1,'')

[$扫地僧$]

create table A
(
parent int,
Child int
)

insert A select 1,2
insert A select 2,3
insert A select 3,4
insert A select 6,7
insert A select 7,8
insert A select 8,9

create Function T_Find(@id int)
returns varchar(50)
as
begin
declare @lev int
declare @T_SQL varchar(100)
declare @T table(parent int,Child int,lev int)
set @lev=1
set @T_SQL=''
insert @T select parent,Child,@lev from A where parent=@id
while @@rowcount>0
begin
set @lev=@lev+1
insert @T select parent,Child,@lev from A where parent in(select Child from @T where lev=@lev-1)
end
select @T_SQL=@T_SQL+ cast(Child as varchar) + ',' from @T
return left(@T_SQL,len(@T_SQL)-1)
end

select dbo.T_Find(1)
--or

create Function T_Find(@id int)
returns @T table(parent int,Child int,lev int)
as
begin
declare @lev int
--declare @T_SQL varchar(100)
--declare @T table(parent int,Child int,lev int)
set @lev=1
--set @T_SQL=''
insert @T select parent,Child,@lev from A where parent=@id
while @@rowcount>0
begin
set @lev=@lev+1
insert @T select parent,Child,@lev from A where parent in(select Child from @T where lev=@lev-1)
end
return
end

select Child from dbo.T_Find(1)

[rivery]

--辅助函数
create function f_getchildid(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select child,@l from ttest where parent=@id
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.child,@l from ttest a join @re b on a.parent=b.id where b.level=@l-1
end
--delete @re where id=@id 这里多余了，忘记删除。
return
end
go

[paoluo]

得到字符串的形式

--建立測試環境
Create Table Tree
(Parent Int,
Child Int)
--插入數據
Insert Tree Select 1, 2
Union All Select 2, 3
Union All Select 3, 4
Union All Select 6, 7
Union All Select 7, 8
Union All Select 8, 9
Go
--建立函數
Create Function GetChild(@Parent Int)
Returns Varchar(1000)
As
Begin
Declare @S Varchar(1000)
Declare @Child Table(Parent Int,Child Int)
Set @S=''
Insert @Child Select * From Tree Where Parent=@Parent
While @@ROWCOUNT>0
Insert @Child Select B.* From @Child A Inner Join Tree B On A.Child=B.Parent Where B.Parent Not In (Select Distinct Parent From @Child)
Select @S=@S+','+Rtrim(Child) From @Child Order By Child
Return(Stuff(@S,1,1,''))
End
GO
--測試
Select dbo.GetChild(1) As Child
Select dbo.GetChild(6) As Child
Select dbo.GetChild(2) As Child
GO
--刪除測試環境
Drop Table Tree
Drop Function GetChild
Go
--結果
/*
Child
2,3,4

Child
7,8,9

Child
3,4
*/

[itblog]

CREATE TABLE tb( Parent int,Child int)
INSERT tb SELECT 1,2
UNION ALL SELECT 2,3
UNION ALL SELECT 3,4
UNION ALL SELECT 6,7
UNION ALL SELECT 7,8
UNION ALL SELECT 8,9

GO

--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID int)
RETURNS varchar(800)
AS
BEGIN
declare @t_Level TABLE(ID int,Level int)
declare @re varchar(800)
select @re=''
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.Child,@Level
FROM tb a,@t_Level b
WHERE a.Parent=b.ID
AND b.Level=@Level-1

END
select @re=@re+','+Rtrim(id) from @t_level
return(stuff(@re,1,3,''))
END
GO

select distinct 子节点=dbo.f_Cid(1) from tb


--结果
/*
2,3,4
*/

[goes_kad]

谢谢大家, 不过这样还是无法满足需求, 因为不能使用存储过程, 我现在的策略是把所有纪录都查出来, 然后在外部判断, 如果表中的纪录不太多, 性能上还是不错的