SQL递归函数列出父级的所有子级

--SQL递归函数列出父级的所有子级(ID ParentID模式)

-- 生成测试数据   
create   table  Dept(ID  int ,ParentID  int ,msg  varchar ( 20 ))   
insert   into  Dept  select   1 , 0 , null   
insert   into  Dept  select   2 , 1 , null   
insert   into  Dept  select   3 , 1 , null   
insert   into  Dept  select   4 , 2 , null   
insert   into  Dept  select   5 , 3 , null   
insert   into  Dept  select   6 , 5 , null   
insert   into  Dept  select   7 , 6 , null   
go    


-- 调用方法：   
-- select * from GetChild('24')   
-- select id from GetChild('24')   
-- select * from KuCun where ProductType in(select id from GetChild('24'))   
  
Create   function   [ dbo ] . [ GetChild ] ( @ID   varchar ( 10 ))   
returns   @t   table (ID  varchar ( 10 ),ParentID  varchar ( 10 ), Level   int )   
as   
begin   
     declare   @i   int   
     set   @i   =   1    
     insert   into   @t   select   @ID , @ID , 0   -- 当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作   
     insert   into   @t   select  ID,ParentID, @i   from  sys_org_info  where  ParentID  =   @ID    
  
     while   @@rowcount <> 0    
     begin   
         set   @i   =   @i   +   1    
         insert   into   @t    
         select   
            a.ID,a.ParentID, @i    
         from   
            sys_org_info a, @t  b   
         where   
            a.ParentID = b.ID  and  b. Level   =   @i - 1    
     end   
     return   
end   


-- 执行查询   
select  ID  from  dbo.GetChild( 3 )   
go   

 

--在SQL Server2005中其实提供了CTE[公共表表达式]来实现递归:   
Declare @Id Int   
Set @Id = 24;    ---在此修改父节点   
 
With RootNodeCTE(Id,ParentId)   
As   
(   
Select Id,ParentId From Dept Where ParentId In (@Id)   
Union All   
Select Dept.Id,Dept.ParentId From RootNodeCTE   
Inner Join Dept  
On RootNodeCTE.Id = Dept.ParentId   
)
Select * From RootNodeCTE 

转载于:https://www.cnblogs.com/jdmei520/archive/2009/09/18/1569432.html