来自员工表的经理/主管/员工层次结构的 SQL 报告 [英] SQL Reports for Manager/Supervisor/Employee Hierarchy from an Employee Table

问题描述

我有一个员工表，其中显示了员工 ID 和他们的经理 ID.经理们想要的报告不仅要显示他的直接下属，还要显示他自己和所有向他的直接下属报告的员工.

I have an Employee table which show an employee ID and their manager ID. The managers want reports that will not only show his direct reports but also himself and all those employees who report to his direct reports.

例如，我的表将有下表:

For example, my table would have the following table:

我需要为 Manager1 编写的报告将包含他的信息以及他的直接下属(Supervisor1 和 Supervisor2)及其报告(Employee1 到 Employee4).

The report I need to write for Manager1 would have his information as well as his direct reports (Supervisor1 and Supervisor2) and their reports (Employee1 through Employee4).

我可以使用某种连接或任何其他代码将这张表多次与自身相关联以获取间接报告吗?

Is there some kind of Join I can use or any other code to relate this one table back to itself multiple time to pick up the indirect reports?

推荐答案

A 递归公用表表达式就是你要找的:

declare @Employees table (EmpID nvarchar(15), MgrID nvarchar(15));

insert into @Employees values ('Manager1', null);
insert into @Employees values ('Supervisor1', 'Manager1');
insert into @Employees values ('Employee1', 'Supervisor1');
insert into @Employees values ('Employee2', 'Supervisor1');
insert into @Employees values ('Supervisor2', 'Manager1');
insert into @Employees values ('Employee3', 'Supervisor2');
insert into @Employees values ('Employee4', 'Supervisor2');

with Employees (MgrID, EmpID, [Rank], [Reports]) as
(
    select
      MgrID
    , EmpID
    , 0 'Rank'
    , cast(EmpID as nvarchar(max)) 'Reports'
    from @Employees
    where MgrID is null
    union all
    select
      e.MgrID 
    , e.EmpID
    , [Rank] + 1
    , [Reports] + '; ' + e.EmpID
    from @Employees e
    inner join Employees on Employees.EmpID = e.MgrID
)
select *
from Employees
where Reports like 'Manager1%'
order by [Rank];

这篇关于来自员工表的经理/主管/员工层次结构的 SQL 报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！