如何更新Server中的树视图

Question

这是我的Server表

ID      Job                       ParentID    MyTeam
1       CEO                       NULL        
2       CFO                       1
3       CTO                       1
4       CMO                       1
5       Accounting Manager        2
6       Payroll Manager           2
7       Data Manager              3
8       Software Manager          3
9       Data Analyst              7
10      Data Engineer             7

我需要这样填充MyTeam字段

每一项工作都将由所有的人来管理。

首席执行官的团队将是CEO, CFO, CTO, CMO, Accounting Manager, Payroll Manager, Data Manager, Software Manager, Data Analyst, Data Engineer

首席财务官的团队将是CFO, Accounting Manager, Payroll Manager

首席技术官的团队将是CTO, Data Manager, Software Manager, Data Analyst, Data Engineer

我在这些数据上构建了一个循环，并将每个作业与其父作业联系起来，诸如此类。

但这太慢了

是否有一个更快的更新语句可以这么快地完成？

Answer 1

为此，可以使用递归CTE。

首先，获取所有员工，对于每个员工，如果有其子行，则使用递归。请注意，我们这里没有做任何循环检查，假设没有循环。

然后根据您开始使用的顶部ID聚合它，并将其连接回主表。

WITH cte AS (
    SELECT e.ID, e.Job, e.ID AS TopID
    FROM Employee e

    UNION ALL

    SELECT e.ID, e.Job, cte.TopID
    FROM cte
    JOIN Employee e ON e.ParentID = cte.ID
)
UPDATE e
SET MyTeam = grouped.Jobs
FROM Employee e
JOIN (
    SELECT
      cte.TopID,
      STRING_AGG(cte.Job, ', ') AS Jobs
    FROM cte
    GROUP BY
      cte.TopID
) grouped ON grouped.TopID = e.ID;

还可以通过计算CTE中的Level列来强制排序。

WITH cte AS (
    SELECT e.ID, e.Job, e.ID AS TopID, 1 AS Level
    FROM Employee e

    UNION ALL

    SELECT e.ID, e.Job, cte.TopID, cte.Level + 1
    FROM cte
    JOIN Employee e ON e.ParentID = cte.ID
)
UPDATE e
SET MyTeam = grouped.Jobs
FROM Employee e
JOIN (
    SELECT
      cte.TopID,
      STRING_AGG(cte.Job, ', ') WITHIN GROUP (ORDER BY cte.Level) AS Jobs
    FROM cte
    GROUP BY
      cte.TopID
) grouped ON grouped.TopID = e.ID;

db<>fiddle

Answer 2

这种方法使用循环，但应该相对较快。

• 它是根据作业中的级别数运行的，而不是基于个人(因此，如果您有3个类似于这里的级别，它会执行3个循环)。
• 读取和计算非常有效-它不需要做大量的连接和排序以及表扫描(除了第一个)。

在这把小提琴中有一些运行的例子，还有一些额外的报告。

/*  Initial data setup  */

CREATE TABLE #Emps (ID int PRIMARY KEY, Job nvarchar(30), ParentID int);
INSERT INTO #Emps (ID, Job, ParentID) VALUES
(1,       N'CEO',                       NULL),
(2,       N'CFO',                       1),
(3,       N'CTO',                       1),
(4,       N'CMO',                       1),
(5,       N'Accounting Manager',        2),
(6,       N'Payroll Manager',           2),
(7,       N'Data Manager',              3),
(8,       N'Software Manager',          3),
(9,       N'Data Analyst',              7),
(10,      N'Data Engineer ',            7);

CREATE TABLE #EmpParents (RoundNum int, ID int, ParentID int, PRIMARY KEY (RoundNum, ID));

每个“循环”(例如，循环中的迭代次数)的数据被插入到#EmpParents表中。

方法是：(首先包括基本作业，而父作业b)，然后对于父作业，标识他们的父作业重复b)，直到找不到更多的父作业。

找到“父母的父母”的方法是查看循环中的最后一轮，以从该轮中获取每个作业的父级ID；在下一轮中，它使用这些新的父母来识别下一个级别的父母。

请注意，我使用WHILE循环完成了这一操作，因为我认为它更容易阅读/理解。但是，您可以使用游标或递归CTE来完成这一任务，但它们无论如何都是循环中的变体。

/*  Data calculations - Find parents, and parents of parents  */

DECLARE @RoundNum int;     -- Used for counting what iteration we're up to
DECLARE @NumInserted int;  -- User for recording number of rows inserted

-- Insert initial data (own job and parent job)
INSERT INTO #EmpParents (RoundNum, ID, ParentID)
    SELECT  0, ID, ID
    FROM    #Emps
  UNION ALL
    SELECT  1, ID, ParentID
    FROM    #Emps
    WHERE   ParentID IS NOT NULL;

SET @NumInserted = @@ROWCOUNT;
SET @RoundNum = 1;

-- Add 'parents of parents' until no more found
WHILE @NumInserted > 0
    BEGIN

    SET @RoundNum += 1;

    INSERT INTO #EmpParents (RoundNum, ID, ParentID)
        SELECT  @RoundNum, #EmpParents.ID, #Emps.ParentID
        FROM    #EmpParents
                INNER JOIN #Emps ON #EmpParents.ParentID = #Emps.ID
        WHERE   #EmpParents.RoundNum = @RoundNum - 1
                AND #Emps.ParentID IS NOT NULL;

    SET @NumInserted = @@ROWCOUNT;

    END

在最后的报告中，请注意，它使用最后一列(ParentID)作为主列，并查找与其对应的所有作业ID。

/*  Reporting  */

SELECT      ParentEmp.Job, STRING_AGG(TeamEmp.Job, ', ') AS Team
    FROM    #EmpParents
            INNER JOIN #Emps AS ParentEmp ON #EmpParents.ParentID = ParentEmp.ID
            INNER JOIN #Emps AS TeamEmp ON #EmpParents.ID = TeamEmp.ID
    GROUP BY #EmpParents.ParentID, ParentEmp.Job
    ORDER BY #EmpParents.ParentID;

以下是结果

Job                            Team
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CEO                            CEO, CFO, CTO, CMO, Accounting Manager, Payroll Manager, Data Manager, Software Manager, Data Analyst, Data Engineer 
CFO                            Accounting Manager, Payroll Manager, CFO
CTO                            CTO, Data Manager, Software Manager, Data Analyst, Data Engineer 
CMO                            CMO
Accounting Manager             Accounting Manager
Payroll Manager                Payroll Manager
Data Manager                   Data Manager, Data Analyst, Data Engineer 
Software Manager               Software Manager
Data Analyst                   Data Analyst
Data Engineer                  Data Engineer 

最后注意:这里没有错误检查，并且采用了等级结构(例如，您不能有一个家长循环，例如，我的下属是我老板的老板)。您可能需要在循环/etc中添加一些错误检查来控制这个错误。一个简单的问题是，如果RoundNum达到了一个不可能的、不切实际的高数字(例如50)，那么它就会因错误而中止。