如何列出特定员工下的所有员工？

Select * 
from IWIN..VW_EMPMASTER E 
where E.em_teamcd = 'C0015'

gives me:

C3892
C6331

which is list of employees which come immediately under C0015

这是直接来自C0015的员工名单

Select * 
from IWIN..VW_EMPMASTER E 
where E.em_teamcd = 'C3892'

gives me list of all 14 employees which come immediately under C3892

给我列出了直接受C3892影响的所有14名员工

I want to get list of ALL employees which come under 'em_empid' C0015 directly or indirectly.. and not just employees which come directly under him. How can I do so ?

我想获得直接或间接来自'em_empid'C0015的所有员工的名单......而不仅仅是直接来自他的员工。我怎么能这样做?

Maximum level of hierarchy is 5.

最高层次结构为5。

1 个解决方案

#1

Assuming em_teamcdis the identifier for the manager and em_empididentifies employees (including managers) you can use a recursive query using a common table expression, I adapted this from an example in the documentation:

假设em_teamcdis是管理器和em_empididentifies员工(包括管理员)的标识符,您可以使用公共表表达式使用递归查询,我从文档中的示例进行了调整:

WITH Tree (em_empid, em_teamcd, level)
AS
(
-- Anchor member definition
    SELECT e.em_empid, e.em_teamcd, 0 AS Level
    FROM dbo.VW_EMPMASTER AS e
    WHERE em_teamcd = 'C0015'
    UNION ALL
-- Recursive member definition
    SELECT e.em_empid, e.em_teamcd, Level + 1
    FROM dbo.VW_EMPMASTER AS e
    INNER JOIN Tree AS d ON e.em_teamcd = d.em_empid
)
-- Statement that executes the CTE
SELECT * FROM Tree WHERE Level <= 5

The column em_empidwill hold all employees that has C0015above them.

列em_empid将保留所有拥有C0015的员工。

A sample SQL Fiddle with some fake data.

一个示例SQL小提琴与一些假数据。

#1