• 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;
    

      

     

  • 相关阅读:
    nginx
    DNS
    lrzsz上传下载命令
    linux命令大全20180614
    解决Nginx的connect() to 127.0.0.1:8080 failed (13: Permission denied) while connect
    安装nginx
    scp复制
    Vue3中报错“export ‘createWebHistory, createRouter‘ was not found in ‘vue-router‘
    点击div,span, p这些文本标签时隐藏光标的方法
    el-upload使用beforeUpload不生效
  • 原文地址:https://www.cnblogs.com/BounceGuo/p/10272759.html
Copyright © 2020-2023  润新知