mssql -递归查询组织机构某节点下所有及最底层，重点：按顶级分组

0.原数据

1.查询某一节点下的所有节点

;WITH OrganizeTree AS ( SELECT OrganizationalName, ParentOrganizationalID, OrganizationalID FROM aapbu.Organizational
--WHERE ParentOrganizationalID = '1001'
UNION ALL
SELECT ot.OrganizationalName, ot.ParentOrganizationalID, ot.OrganizationalID from OrganizeTree
JOIN aapbu.Organizational ot on OrganizeTree.OrganizationalID = ot.ParentOrganizationalID
)
SELECT OrganizationalName, ParentOrganizationalID, OrganizationalID FROM OrganizeTree

mssql -递归查询组织机构某节点下所有及最底层，重点：按顶级分组

2.查询某一节点下的最底层节点
;WITH OrganizeTree AS ( SELECT OrganizationalName, ParentOrganizationalID, OrganizationalID FROM aapbu.Organizational
--WHERE ParentOrganizationalID = '1001'
UNION ALL
SELECT ot.OrganizationalName, ot.ParentOrganizationalID, ot.OrganizationalID from OrganizeTree
JOIN aapbu.Organizational ot on OrganizeTree.OrganizationalID = ot.ParentOrganizationalID)
,leaf as (
select l.OrganizationalName,l.ParentOrganizationalID,l.OrganizationalID from OrganizeTree l
WHERE NOT EXISTS(SELECT c.OrganizationalName, c.ParentOrganizationalID, c.OrganizationalID FROM OrganizeTree c WHERE l.OrganizationalID = c.ParentOrganizationalID)
)
SELECT * FROM leaf

mssql -递归查询组织机构某节点下所有及最底层，重点：按顶级分组

3.应用重点：查询根节点下递归子节点，并且按根节点分组！

;WITH OrganizeTop AS (
   SELECT OrganizationalName, ParentOrganizationalID, OrganizationalID,IsDisable FROM aapbu.Organizational
   WHERE IsDisable=0 and OrganizationalID in(select OrganizationalID from aapbu.Organizational where ParentOrganizationalID
=(select OrganizationalID FROM aapbu.Organizational where ParentOrganizationalID ='' ) and IsDisable=0)
)
SELECT ROW_NUMBER() over(order by OrganizationalID) as row,OrganizationalName, ParentOrganizationalID, OrganizationalID,IsDisable,'' ppd into #OrganizeDemo FROM OrganizeTop

declare @count int;
declare @i int;
declare @topparent nvarchar(50);
select @count = COUNT(1) from #OrganizeDemo;
set @i = 1;
while (@count >= @i)
begin
select @topparent = OrganizationalID from #OrganizeDemo where [row] = @i;
       ;WITH OrganizeTree AS (
           SELECT OrganizationalName, ParentOrganizationalID, OrganizationalID,IsDisable FROM aapbu.Organizational
           WHERE IsDisable=0 and [email protected]
           UNION ALL
           SELECT ot.OrganizationalName, ot.ParentOrganizationalID, ot.OrganizationalID,ot.IsDisable from OrganizeTree
           JOIN aapbu.Organizational ot on OrganizeTree.OrganizationalID = ot.ParentOrganizationalID
           where ot.IsDisable=0
       )
       SELECT OrganizationalName, ParentOrganizationalID, OrganizationalID,IsDisable,@topparent as ppd into #OrganizeDemo1 FROM OrganizeTree

       if not exists(select * from tempdb..sysobjects where id=object_id('tempdb..#OrganizeDemoGroup'))
           SELECT * into #OrganizeDemoGroup FROM #OrganizeDemo1
       ELSE
           insert into #OrganizeDemoGroup( OrganizationalName, ParentOrganizationalID, OrganizationalID,IsDisable,ppd)
               SELECT * FROM #OrganizeDemo1

Drop table #OrganizeDemo1;

set @i = @i + 1;
end

Drop table #OrganizeDemo;
select * FROM #OrganizeDemoGroup
Drop table #OrganizeDemoGroup;

mssql -递归查询组织机构某节点下所有及最底层，重点：按顶级分组

PS:用了临时表完成此功能，如果有更好的，请大神指点一二，谢谢

mssql -递归查询组织机构某节点下所有及最底层，重点：按顶级分组

相关推荐