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

发布于 2025-01-02 12:00:29 字数 8 浏览 1 评论 0原文

continue

I have an update that has to be made. And Im really stuck.
This is a classic hierarchical employee table question, but with a twist.
Please look at this tree of users:
employees

(Completely unrelated to my problem, just something I found with google pictures)

Lets assume the following Id's:

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

I need to add another column, lets call it parentSpecialId.
This id is the id of the user below root (AM1 and AM2).
All users below AM1 and AM2 should have the parentSpecialId set to the user below root.

Which gives us:

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

All I have is this CTE which gives me a result set with AM1 and AM2.
So I need to traverse all way down to EMPX and update parentSpecialId with Id 2 for
AM1 and the same for all users for AM2. Of course, it needs to by dynamic, in real life I have 12 of these users below root.

Does it make sense?

Here is my 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

Oh, and I use Sql server 2008 R2

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无法言说的痛 2025-01-09 12:00:29

continue

Sample data:

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);

Update statement:

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

回复收藏 0 原文

霓裳挽歌倾城醉 2025-01-09 12:00:29

处理任意深度的树：

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 设置示例数据！

To handle a tree of arbitrary depth:

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

With thanks to Mikael Eriksson for setting up the sample data!

回复收藏 0 原文

~没有更多了~