SQL基础（五）-- 递归

递归一般出现在树形结构中

     根据父节点查找所有的子节点：

With T
As
(
SELECT * FROM OverseaECommerceManagement.dbo.ProductReview_Detail PRR WITH(NOLOCK) WHERE PRR.SysNo=167154
Union All
Select PRR.* From  OverseaECommerceManagement.dbo.ProductReview_Detail PRR WITH(NOLOCK) Inner Join T on PRR.ReferenceSysNo=T.SysNo
)
Select * From T

　　

         根据子节点查找所有父节点：

With T
As
(
Select * From OverseaECommerceManagement.dbo.ProductReview_Detail PRR WITH(NOLOCK) WHERE PRR.SysNo=167255
Union All
Select PRR.* From  OverseaECommerceManagement.dbo.ProductReview_Detail PRR WITH(NOLOCK) Inner Join T on PRR.SysNo=T.ReferenceSysNo
)
Select * From T

　　SplitStrToTable函数代码：

ALTER   function   [dbo].[SplitStrToTable]
(
    @SourceSql   VARCHAR(8000),
    @StrSeprate  VARCHAR(100))
    RETURNS   @temp   TABLE(F1   VARCHAR(100)
)  
AS    
BEGIN  
  DECLARE   @ch  AS   VARCHAR(100)  
  SET   @SourceSql=@SourceSql+@StrSeprate    
  WHILE(@SourceSql<>'')  
  BEGIN  
        SET   @ch=left(@SourceSql,CHARINDEX(',',@SourceSql,1)-1)  
        INSERT   @temp   VALUES(@ch)  
        SET   @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'')  
  END  
  RETURN  
END

　　如果有多个子节点：

 

declare @CompanyRange varchar(1000)=''
set @CompanyRange='80084,80085'
 
;With T
    As
    (
        SELECT Result.* FROM
        (
            SELECT PRR1.* From
                OverseaECommerceManagement.dbo.ProductReview_Detail PRR1 WITH(NOLOCK) 
                INNER JOIN
                (SELECT F1 AS ReferenceSysNo  FROM dbo.SplitStrToTable(@CompanyRange,',')) T2
            ON PRR1.ReferenceSysNo=T2.ReferenceSysNo
        ) Result
    Union All
    Select Result.*
    From OverseaECommerceManagement.dbo.ProductReview_Detail Result WITH(NOLOCK)  Inner Join T on Result.SysNo=T.ReferenceSysNo
    )
    Select * From T ORDER BY ReferenceSysNo;