SqlServer父节点与子节点查询及递归代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】 SqlServer父节点与子节点查询及递归

作者:Dezai.CN / 发布于2012/11/21/ 841

/*  
标题: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

试试其它关键字

　递归　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)