前言:
在实际开发中,我们经常需要根据一个父节点获取所有的子节点,也就是所谓的递归.有的时候也需要根据子节点,获取它的父节点.
下面我介绍两种方式实现上面的需求:
- 用户自定义函数方式(Sql Server 2000+)
- 公用表表达式方式(Sql Server 2005+)
- Hierarchyid方式(Sql Server 2008+)--待研究
实现:
- /*
- 标题:sql server中递归的实现
- 作者:axin
- 时间:2012-3-24
- */
- set nocount on
- if OBJECT_ID('tb','U') is not null drop table tb
- go
- create table tb(ID int,PID INT)
- insert into tb
- select 1,0 union all
- select 2,1 union all
- select 3,2 union all
- select 4,3 union ALL
- select 5,4 union ALL
- select 6,5 union ALL
- select 7,6
- --自定义函数方式实现父节点查询子节点
- if OBJECT_ID('GetChildID') is not null drop function GetChildID
- go
- create function GetChildID(@ParentID int)
- returns @t table(ID int)
- as
- begin
- insert into @t select ID from tb where PID=@ParentID
- while @@rowcount<>0
- begin
- insert into @t select a.ID from tb as a
- inner join @t as b
- on a.PID=b.ID
- and not exists(select 1 from @t where ID=a.ID)
- end
- return
- end
- go
- select * from dbo.GetChildID(1)
- --自定义函数方式实现子节点查询父节点
- if OBJECT_ID('GetParentID') is not null drop function GetParentID
- go
- create function GetParentID(@ChildID int)
- returns @t table(PID int)
- as
- begin
- insert into @t select PID from tb where ID=@ChildID
- while @@rowcount<>0
- begin
- insert into @t select a.PID from tb as a
- inner join @t as b
- on a.ID=b.PID
- and not exists(select 1 from @t where PID=a.PID)
- end
- return
- end
- go
- select * from dbo.GetParentID(3)
- --公用表表达式实现父节点查询子节点(SqlServer2005+)
- DECLARE @ParentID int
- SET @ParentID=1
- with CTEGetChild as
- (
- select * from tb where PID=@ParentID
- UNION ALL
- (SELECT a.* from tb as a inner join
- CTEGetChild as b on a.PID=b.ID
- )
- )
- SELECT * FROM CTEGetChild
- --公用表表达式实现子节点查询父节点(SqlServer2005+)
- DECLARE @ChildID int
- SET @ChildID=6
- DECLARE @CETParentID int
- select @CETParentID=PID FROM tb where ID=@ChildID
- with CTEGetParent as
- (
- select * from tb where ID=@CETParentID
- UNION ALL
- (SELECT a.* from tb as a inner join
- CTEGetParent as b on a.ID=b.PID
- )
- )
- SELECT * FROM CTEGetParent