详见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