SqlServer父节点与子节点查询及递归
来源:互联网 发布:淘宝店铺哪里购买 编辑:程序博客网 时间:2024/04/30 05:52
- /*
- 标题: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)
-
- 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
-
- 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