更新分层员工表中的特殊id

Question

我有一个必须进行的更新。我真的被卡住了。这是一个经典的分层员工表问题，但有一个转折点。请查看此用户树：员工

（与我的问题完全无关，只是我在谷歌图片中发现的东西）

让我们假设以下Id：

RM1: EmpId 1, ParentId null
AM1: EmpId 2, ParentId 1
MGR1: EmpId 3, ParentId 2
MGR2: EmpId 4, ParentId 2
EMP1: EmpId 5, ParentId 3
EMP2: EmpId 6, ParentId 3
EMP3: EmpId 7, ParentId 4
EMP4: EmpId 8, ParentId 4

我需要添加另一列，称之为parentSpecialId。这个id是root（AM1和AM2）下面的用户的id。AM1和AM2以下的所有用户都应将parentSpecialId设置为root以下的用户。

这给了我们：

RM1: EmpId 1, ParentId null parentSpecialId null
AM1: EmpId 2, ParentId 1    parentSpecialId null
MGR1: EmpId 3, ParentId 2   parentSpecialId 2
MGR2: EmpId 4, ParentId 2   parentSpecialId 2
EMP1: EmpId 5, ParentId 3   parentSpecialId 2
EMP2: EmpId 6, ParentId 3   parentSpecialId 2
EMP3: EmpId 7, ParentId 4   parentSpecialId 2
EMP4: EmpId 8, ParentId 4   parentSpecialId 2

我所拥有的只是这个CTE，它给了我一个AM1和AM2的结果集。因此，我需要一直遍历到EMPX，并将parentSpecialId更新为Id 2AM1，对于AM2的所有用户也是如此。当然，它需要动态，在现实生活中，我有12个这样的用户在root以下。

这有道理吗？

这是我的CTE:

    WITH EmpsCTE AS
(
    SELECT id, parent, name, 0 AS EmployeeLevel
    FROM Employee
    WHERE parent = 0 
    UNION ALL
    SELECT e.id, e.parent, e.name, EmployeeLevel + 1
    FROM EmpsCTE AS p 
    JOIN Employee AS e ON e.parent = p.id 
)
SELECT id, parent, name, EmployeeLevel
From EmpsCTE where EmployeeLevel = 1 

哦，我使用Sql server 2008 R2

Answer 1

样本数据：

declare @T table
(
  Name varchar(10),
  EmpId int,
  ParentId int,
  ParentSpecialID int
);
insert into @T(Name, EmpId, ParentId) values
('RM1',  1, null),
('AM1',  2, 1),
('MGR1', 3, 2),
('MGR2', 4, 2),
('EMP1', 5, 3),
('EMP2', 6, 3),
('EMP3', 7, 4),
('EMP4', 8, 4);

更新声明：

with C as
(
  select T3.EmpId,
         T2.EmpId as ParentSpecialId
  from @T as T1
    inner join @T as T2
      on T1.EmpId = T2.ParentId
    inner join @T as T3
      on T2.EmpId = T3.ParentId  
  where T1.ParentId is null
  union all
  select T.EmpId,
         C.ParentSpecialId
  from @T as T
    inner join C
      on T.ParentId = C.EmpId       
)
update T
set ParentSpecialId = C.ParentSpecialId
from @T as T
  inner join C
    on T.EmpId = C.EmpId

Answer 2

处理任意深度的树：

declare @T table ( Name varchar(16), EmpId int, ParentId int ); 
insert into @T(Name, EmpId, ParentId) values 
  ('RM1',  1, null),
  ('AM1',  2, 1), 
  ('MGR1', 3, 2), 
  ('MGR2', 4, 2), 
  ('EMP1', 5, 3), 
  ('EMP2', 6, 3), 
  ('EMP3', 7, 4), 
  ('EMP4', 8, 4),
  ('AM2', 9, 1),
  ('MGR3', 10, 9),
  ('EMP5', 11, 10),
  ('Brown Noser', 12, 11),
  ('Intern', 13, 12),
  ('Coop', 14, 13),
  ('Nephew', 15, 14),
  ('Contractor', 16, 15);
; with CTE as (
  -- Start with the root(s).
  select Name, EmpId, ParentId, 0 as Depth, Cast(NULL as Int) as parentSpecialId
    from @T
    where ParentId is NULL
  union all
  -- Add the direct reports one layer at a time.
  select T.Name, T.EmpId, T.ParentId, CTE.Depth + 1, case when CTE.Depth = 1 then T.ParentId else CTE.parentSpecialId end
    from CTE inner join
      @T as T on T.ParentId = CTE.EmpID
    where T.ParentId = CTE.EmpId
  )
select *,
  ( select Name from CTE as R where R.EmpId = CTE.ParentId ) as ReportsTo,
  ( select Name from CTE as SC where SC.EmpId = CTE.parentSpecialId ) as SubCommander
  from CTE
  order by Depth, Name

感谢Mikael Eriksson设置样本数据！