树形结构的表中，如何返回一个儿子的所有上级或一个上级的所有子子孙孙？

[dejoy]

一个表T,ParentID -1是最上级
ParentID ID
-1 1
1 2
1 3
2 8
8 12
生成的树是这样的：
1－|
2－|
| 8－|
| 12
3－
1.现在如何返回一个值的所有上级列表，如返回12的所有上级
ID
8
2
1
-1
2.如何返回一个上级的所有下级，如返回2的所有下级
ID
8
12
这么做主要是判断谁和谁的上下级关系。

附SQL：
CREATE TABLE T (
ParentID INTEGER,
ID INTEGER
)
GO

COMMIT
GO

INSERT INTO [T] ([ParentID], [ID])
VALUES
(-1, 1)
GO

INSERT INTO [T] ([ParentID], [ID])
VALUES
(1, 3)
GO

INSERT INTO [T] ([ParentID], [ID])
VALUES
(1, 4)
GO

INSERT INTO [T] ([ParentID], [ID])
VALUES
(1, 2)
GO

INSERT INTO [T] ([ParentID], [ID])
VALUES
(2, 8)
GO

INSERT INTO [T] ([ParentID], [ID])
VALUES
(8, 12)
GO

COMMIT
GO

[zjcxc]

--树形数据查询示例
--作者: 邹建

if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb]
GO

--示例数据
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'江苏'
union all select 6,'苏州'
union all select 7,'常熟'
union all select 6,'南京'
union all select 6,'无锡'
union all select 2,'纽约'
union all select 2,'旧金山'
go



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_cid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_cid]
GO

/*--树形数据处理

查询指定id的所有子

--邹建 2003-12(引用请保留此信息)--*/

/*--调用示例

--调用(查询所有的子)
select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
--*/
create function f_cid(
@id int
)returns @re table([id] int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l
from [tb] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
/*--如果只显示最明细的子(下面没有子),则加上这个删除
delete a from @re a
where exists(
select 1 from [tb] where [pid]=a.[id])
--*/
return
end
go

--调用(查询所有的子)
select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
go



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_pid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_pid]
GO

/*--树形数据处理


查询指定id的所有父

--邹建 2003-12(引用请保留此信息)--*/

/*--调用示例

--调用(查询所有的父)
select a.* from [tb] a,f_pid(7)b where a.[id]=b.[id]
--*/
create function f_pid(
@id int
)returns @re table([id] int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select [pid],@l from [tb] where [id]=@id and [pid]<>0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[pid],@l
from [tb] a,@re b
where a.[id]=b.[id] and b.[level]=@l-1 and a.[pid]<>0
end
return
end
go

--调用(查询所有的父)
select a.* from [tb] a,f_pid(7)b where a.[id]=b.[id]
go

[子陌红尘]

create function f_getchild(@ID int)
returns @t table(ParentID int,ID int)
as
begin
insert into @t select * from 表 where ParentID=@ID
while @@rowcount<>0
begin
insert into @t
select
a.*
from
表 a,@t b
where
a.ParentID=b.ID and not exists(select 1 from @t where ID=a.ID)
end
return @t
end
go


select ID from dbo.f_getChild(2)
go




create function f_getparent(@ID int)
returns @t table(ParentID int,ID int)
as
begin
insert into @t
select * from 表 where ID=(select ParentID from 表 where ID=@ID)

while @@rowcount<>0
begin
insert into @t
select
a.*
from
表 a,@t b
where
a.ID=b.ParentID and not exists(select 1 from @t where ID=a.ID)
end
return @t
end
go


select ID from dbo.f_getparent(8)
go

[dejoy]

请zjcxc(邹建)，再看一下
http://community.csdn.net/Expert/topic/4857/4857244.xml?temp=.6091272

[paoluo]

2.


--建立函數
Create Function GetChild(@ParentID Int)
Returns @Child Table(ParentID Int,ID Int)
As
Begin
Insert @Child Select * From T Where ParentID=@ParentID
While @@ROWCOUNT>0
Insert @Child Select B.* From @Child A Inner Join T B On A.ID=B.ParentID Where B.ParentID Not In (Select Distinct ParentID From @Child)
Return
End
GO
--測試
Select ID From dbo.GetChild(2)
GO
--刪除測試環境
Drop Table T
Drop Function GetChild
Go
--結果
/*
ID
8
12
*/

[dejoy]

请问
Insert @Child Select * From T Where ParentID=@ParentID
While @@ROWCOUNT>0 里的
While @@ROWCOUNT>0 起什么作用？

[paoluo]

@@ROWCOUNT
傳回受到上一個陳述式影響的資料列數目。

[子陌红尘]

如果受影响的记录数大于0，说明递归操作还没有到达根节点（叶节点），则应该继续递归。

[paoluo]

--建立函數
--查詢子節點
Create Function GetChild(@ParentID Int)
Returns @Child Table(ParentID Int,ID Int)
As
Begin
Insert @Child Select * From T Where ParentID=@ParentID
While @@ROWCOUNT>0
Insert @Child Select B.* From @Child A Inner Join T B On A.ID=B.ParentID Where B.ParentID Not In (Select Distinct ParentID From @Child)
Return
End
GO
--查詢父節點
Create Function GetParent(@ID Int)
Returns @Parent Table(ParentID Int,ID Int)
As
Begin
Insert @Parent Select * From T Where ID=@ID
While @@ROWCOUNT>0
Insert @Parent Select B.* From @Parent A Inner Join T B On A.ParentID=B.ID Where B.ID Not In (Select Distinct ID From @Parent)
Return
End
GO
--測試
Select ID From dbo.GetChild(2)
Select ParentID From dbo.GetParent(12)
GO
--刪除測試環境
Drop Table T
Drop Function GetChild
Drop Function GetParent
Go
--結果
/*
ID
8
12

ParentID
8
2
1
-1
*/

[dejoy]

多谢，这里真是高手如云啊

[dejoy]

结贴 ，同时请各位帮忙看看http://community.csdn.net/Expert/topic/4857/4857244.xml?temp=.6091272
后面的两种方法的效率问题。