SQL递归查询函数

-- 查询父单位的函数
CREATE   Function  GetParentUnit( @UnitId   bigint ,  @Level   int ) 
Returns   @Parents   Table ( [ UnitId ]   int  , [ UnitName ]   nvarchar ( 64 ), [ ParentUnitId ]   int ) 
As  
Begin
     If   @Level   <   1     -- 如果参数@Level小于“1”，则得到所有父单位
         Begin         
             Insert   @Parents  
                 Select  UnitId,UnitName,ParentUnitId 
                 From   [ T_Unit ]  
                 Where   [ UnitId ]   =  ( Select   [ ParentUnitId ]  
                 From   [ T_Unit ]   Where   [ UnitId ] = @UnitId )        
            
             While   @@ROWCOUNT   >   0
                 Begin                      
                     Insert   @Parents
                         Select  B.UnitId,B.UnitName,B.ParentUnitId 
                         From   @Parents  A  Inner   Join   [ T_Unit ]  B 
                         On  A. [ ParentUnitId ] = B. [ UnitId ]  
                         Where  B. [ ParentUnitId ]   Not   In  
                        ( Select   Distinct   [ ParentUnitId ]   From   @Parents ) 
                 End
         End
     Else
         Begin
             Set   @level   =   @Level   - 1
            
             Insert   @Parents  
                 Select  UnitId,UnitName,ParentUnitId 
                 From   [ T_Unit ]  
                 Where   [ UnitId ]   =  ( Select   [ ParentUnitId ]  
                 From   [ T_Unit ]   Where   [ UnitId ] = @UnitId )            
            
             While   @@ROWCOUNT   >   0   And   @level   >   0  
                 Begin  
                     Set   @level   =   @Level   - 1  
                     Insert   @Parents
                         Select  B.UnitId,B.UnitName,B.ParentUnitId 
                         From   @Parents  A  Inner   Join   [ T_Unit ]  B 
                         On  A. [ ParentUnitId ] = B. [ UnitId ]  
                         Where  B. [ ParentUnitId ]   Not   In  
                        ( Select   Distinct   [ ParentUnitId ]   From   @Parents ) 
                 End
         End
Return  
End

-- 查询子单位的函数
CREATE   Function  GetChildUnit( @UnitId   bigint ,  @Level   int ) 
Returns   @Child   Table ( [ UnitId ]   int  , [ UnitName ]   varchar ( 64 ), [ ParentUnitId ]   int )
As  
Begin  
     If   @Level   <   1    -- 如果参数@Level小于“1”，则得到所有子单位
         Begin             
             Insert   @Child  
                 Select  UnitId,UnitName,ParentUnitId 
                 From   [ T_Unit ]   Where   [ ParentUnitId ]   =   @UnitId  
             While   @@ROWCOUNT   >   0
                 Begin                      
                     Insert   @Child  
                         Select  B.UnitId,B.UnitName,B.ParentUnitId 
                         From   @Child  A  Inner   Join   [ T_Unit ]  B 
                         On  A. [ UnitId ] = B. [ ParentUnitId ]  
                         Where  B. [ ParentUnitId ]   Not   In  
                        ( Select   Distinct   [ ParentUnitId ]   From   @Child ) 
                 End  
         End
     Else
         Begin
             Set   @level   =   @Level   - 1  
             Insert   @Child  
                 Select  UnitId,UnitName,ParentUnitId 
                 From   [ T_Unit ]   Where   [ ParentUnitId ] = @UnitId  
             While   @@ROWCOUNT   >   0   And   @level   >   0  
                 Begin  
                     Set   @level   =   @Level   - 1  
                     Insert   @Child  
                         Select  B.UnitId,B.UnitName,B.ParentUnitId 
                         From   @Child  A  Inner   Join   [ T_Unit ]  B 
                         On  A. [ UnitId ] = B. [ ParentUnitId ]  
                         Where  B. [ ParentUnitId ]   Not   In  
                        ( Select   Distinct   [ ParentUnitId ]   From   @Child ) 
                 End  
         End     
     Return  
End

-- 查询
select   *   from  GetParentUnit( 3 , 2 )
select   *   from  GetChildUnit( 1 , 2 )

-- 删除函数
drop   Function  GetParentsUnit
drop   Function  GetChildUnit

转载于:https://www.cnblogs.com/EasyData/archive/2007/10/29/941656.html