代码之家 › 专栏 › 技术社区 › BVernon

如何将此游标操作转换为闭包层次结构上基于集合的操作?

transitive-closure-table hierarchical-data hierarchy sql-server-2012 sql

-1

BVernon · 技术社区 · 5 年前

树.DirectReports是一个闭包(层次结构)表。
Users 使用: RowID, EmployeeId, and MangerId . @RowCount 是此表中的记录数 #EmpMgr 是此表的游标。

WHILE @RowCount <= @NumberRecords --loop through each record in Users table
BEGIN       
    SET @EmpId = (SELECT EmployeeId FROM #EmpMgr WHERE RowID = @RowCount)
    SET @MgrId = (SELECT ManagerId FROM #EmpMgr WHERE RowID = @RowCount)

    INSERT INTO [Trees].[DirectReports](EmployeeId, ManagerId, Depth)
    SELECT c.EmployeeId, p.ManagerId, p.Depth + c.Depth + 1
    FROM Trees.DirectReports p join Trees.DirectReports c
    WHERE p.EmployeeId = @MgrId AND c.ManagerId = @EmpId

    SET @RowCount = @RowCount + 1
END

所以我真的很想弄清楚如何把它作为一个集合查询,因为我知道这样做会快得多,但我的大脑现在还没有建立起正确的连接来解决这个问题。

0 回复 | 直到 5 年前

1

2

BVernon 5 年前

WITH cte AS
(
    SELECT LegacyId ancestor, LegacyId descendant, 0 depth FROM Users
    UNION ALL

    SELECT cte.ancestor, u.LegacyId descendant, cte.depth + 1 depth
    FROM   dbo.Users u JOIN cte ON u.ManagerId = cte.descendant
)
select * from cte

然而,一开始让我不舒服的是,有一些坏数据导致了循环依赖。我可以使用以下查询来确定这些实例的位置:

with cte (id,pid,list,is_cycle) 
as
(
    select      legacyid id, managerid pid,',' + cast (legacyid as varchar(max))  + ',',0
    from        users

    union all

    select      u.legacyid id, 
                u.managerid pid, 
                cte.list + cast(u.legacyid as varchar(10)) +  ',' ,case when cte.list like '%,' + cast (u.legacyid as varchar(10)) + ',%' then 1 else 0 end
    from        cte join users u on u.managerid = cte.id
    where       cte.is_cycle = 0
)
select      *
from        cte
where       is_cycle = 1

Is there a way to detect a cycle in Hierarchical Queries in SQL Server? 和 How can I create a closure table using data from an adjacency list?