SQL种获取单表筛选数据的子级或者父级，或者子父级存储过程--通用

详见SQL递归

---------------------------------------获取所有子级

/*----------------------------------------------------------*/
/*    [PC1recursion]                                        */
/*----------------------------------------------------------*/
IF EXISTS ( SELECT 1 FROM sys.objects o WHERE object_id = object_id( N'[PC1recursion]' ) AND OBJECTPROPERTY( object_id, N'IsProcedure') = 1 )
DROP PROCEDURE [PC1recursion]
GO
CREATE PROC [PC1recursion]
(
 @tbname VARCHAR(36) = '', --表明
 @id varchar(50)='',--id名称
 @idValue varchar(36)='',--id值
 @pid varchar(1000) = ''--父级ID名称
)
AS
/*
功能：根据表，查询该表吓所有本级以及所有子级数据
参数：
返回：递归返回所有数据
编写：ljr 2017-07-8
测试:
*/
BEGIN
     declare @sql varchar(max)
     set @sql='WITH cteTree
            AS (SELECT *
                  FROM '+@tbname+'
                  WHERE '+@id+'='''+@idValue+'''  --第一个查询作为递归的基点(锚点)
                UNION ALL
                SELECT '+@tbname+'.*     --第二个查询作为递归成员， 下属成员的结果为空时，此递归结束。
                  FROM
                       cteTree INNER JOIN '+@tbname+' ON cteTree.'+@id+' ='+@tbname+'.'+@pid+') 
                       
        select * into #all from cteTree
        
        select distinct ScreenConfigID,ScreenTemplateID,ScreenType,PScreenConfigID,GroupName
        into #main
        from #all
        
        select m.ScreenConfigID,m.ScreenTemplateID,m.ScreenType,m.PScreenConfigID,m.GroupName,
        c.*
        from #main m
        left join #all c on m.ScreenConfigID=c.ScreenConfigID
        
        drop table #main
        drop table #all
        '
    print @sql   
    exec (@sql)
    END
GO

列子：exec PC1recursion @tbname ='SY1Org'， - 表明
 @id ='orgID'， - id名称
 @idValue ='40730451-F1D4-4A9D-8B57-E3858CA5CA23'， - id值
 @pid ='POrgID'

结果集：

 

树形结果集展示：

 

 

 

 

-------------------------------------------------- - 获取所有父级，子级或者所有

前面写了一个通用的获取子级的，最近项目又遇到获取所有父级的，刚好这几天不是那么忙，于是就把2个整理了一下，写到一个过程里面来了，根据传入参数等获取子级父级等。暂不支持多表关联查询，只支持单表的拉取。

/*----------------------------------------------------------*/
/*    [GetTbChildOrParentList]                              */
/*----------------------------------------------------------*/
IF EXISTS ( SELECT 1 FROM sys.objects o WHERE object_id = object_id( N'[GetTbChildOrParentList]' ) AND OBJECTPROPERTY( object_id, N'IsProcedure') = 1 )
DROP PROCEDURE [GetTbChildOrParentList]
GO
CREATE PROC [dbo].[GetTbChildOrParentList]
(
@tbname VARCHAR(100) = '', --表名
@id varchar(100)='',--id名称
@colomn NVARCHAR(1000)='*',--查询的字段
@sqlWhere varchar(1000)='',--筛选的条件
@type int = 0,--拉取筛选条件的子级还是父级；0 子级 1父级 2所有
@order varchar(100) ='', --排序字段，为空则按照主键排序
@orderType int =0 ,--0 升序， 1降序
@pid varchar(100) = ''--父级ID名称
)
AS
/*
功能：根据查询条件查询表中数据，以及筛选的数据的所有父级
参数：
返回：递归返回所有数据
编写：ljr 2018-10-24
测试:
*/
BEGIN
declare @sql varchar(max)
--先拉取表中所有数据以及筛选的数据set @sql='
        SET NOCOUNT ON
        SELECT * INTO #ALL FROM '+@tbname+'
        CREATE INDEX IX_ALL ON #ALL('+@id+') --给表#ALL建索引
        SELECT * INTO #DATA FROM #ALL WHERE 1=1 AND '+@sqlWhere+'
        CREATE INDEX IX_DATA ON #DATA('+@id+') --给表#DATA建索引
        SELECT * INTO #P FROM #DATA WHERE 1=2--给父级表拷贝表结构
        CREATE INDEX IX_P ON #P('+@id+') --给表#P建索引
        SELECT * INTO #C FROM #DATA WHERE 1=2 --给子级表拷贝表结构
        CREATE INDEX IX_C ON #C('+@id+') --给表#C建索引'        
--然后在递归拉取父级或者子级
set @sql+='
        IF '+CAST(@type AS NVARCHAR(2))+' = 1 OR '+CAST(@type AS NVARCHAR(2))+' = 2 -- 1父级,2所有
        BEGIN
            --递归拉取父级
            ;WITH CTEP AS (
                   SELECT * from #DATA
                   UNION ALL
                   SELECT D.* from CTEP
                   INNER JOIN #ALL D on CTEP.'+@pid+'=D.'+@id+'
            )
            INSERT #P
            SELECT DISTINCT * FROM CTEP ;
        END
        IF '+CAST(@type AS NVARCHAR(2))+' = 0 OR '+CAST(@type AS NVARCHAR(2))+' = 2 --0 子级,,2所有
        BEGIN
            --递归拉取子级
            ;WITH CTEC AS (
                   SELECT * from #DATA
                   UNION ALL
                   SELECT D.* from CTEC
                   INNER JOIN #ALL D on CTEC.'+@id+'=D.'+@pid+'
            )
            INSERT #C
            SELECT DISTINCT * FROM CTEC ;    
        END   
        IF '+CAST(@type AS NVARCHAR(2))+' = 0 --0 子级
        BEGIN
               SELECT '+@colomn+' FROM #C ORDER BY '+ CASE WHEN ISNULL(@order,'')=''THEN @id ELSE @order END +' '+ CASE WHEN @orderType=0 THEN '' ELSE 'DESC' END +'
        END
        ELSE IF '+CAST(@type AS NVARCHAR(2))+' = 1 -- 1父级
        BEGIN
               SELECT '+@colomn+' FROM #P ORDER BY '+ CASE WHEN ISNULL(@order,'')=''THEN @id ELSE @order END +' '+ CASE WHEN @orderType=0 THEN '' ELSE 'DESC' END +'
        END
        ELSE IF '+CAST(@type AS NVARCHAR(2))+' = 2 -- 2所有
        BEGIN
               SELECT *
               FROM (
                SELECT '+@colomn+' FROM #C
                UNION
                SELECT '+@colomn+' FROM #P
               ) A ORDER BY '+ CASE WHEN ISNULL(@order,'')=''THEN @id ELSE @order END +' '+ CASE WHEN @orderType=0 THEN '' ELSE 'DESC' END +'
        END
        --清除临时表
        DROP TABLE #ALL
        DROP TABLE #DATA
        DROP TABLE #P
        DROP TABLE #C
        SET NOCOUNT OFF '
PRINT @sql
EXEC (@sql)
END
GO