使用 CTE 将所有员工置于经理之下 [英] Get all employees under manager with CTE

问题描述

我有一张包含员工详细信息的表格

I have a table which has employee details

EmpId ManagerId Level Value
1      0         5     CEO
2      1         4     EMP
3      1         4     ORG
4      2         3     NULL
5      2         3     NULL
6      2         2     NULL
7      1         1     NULL
8      5         0     NULL

现在，我必须从 Employee Id 2 开始，发现所有它都是低级别的hirerachy(即 2、4、5、6、8)，并为它们分配与2"相同的值(即 EMP).

Now, I have to start wil Employee Id 2 and found all it's low level hirerachy (i.e. 2, 4, 5, 6, 8) and assign them value same as "2" (i.e. EMP).

预期输出:

  EmpId ManagerId Level Value
    1      0         5     CEO
    2      1         4     EMP
    3      1         4     ORG
    4      2         3     EMP
    5      2         3     EMP
    6      2         2     EMP
    7      1         1     NULL
    8      5         0     EMP

我正在尝试什么:

    ; WITH LevelHire AS
(
        SELECT EmpId, ManagerId,Level
        FROM EmployeeTable
        WHERE EmpId =2
        UNION ALL
        SELECT Lh.EmpId,  RC.ManagerId, Lh.Level
        FROM LevelHire LH
        INNER JOIN [EmployeeTable] RC
        ON LH.EmpId= RC.EmpId

)

SELECT * FROM LevelHire
option (maxrecursion 0)

我怎样才能达到同样的目的?

How can I achieve the same?

推荐答案

你可以试试这样的

;WITH EmployeeTable AS 
(
SELECT 1 EmpId,0 ManagerId ,   5 Level ,'CEO' Value
UNION ALL SELECT 2,1,   4,'EMP'
UNION ALL SELECT 3,1,   4,'ORG'
UNION ALL SELECT 4,2,   3,NULL
UNION ALL SELECT 5,2,   3,NULL
UNION ALL SELECT 6,2,   2,NULL
UNION ALL SELECT 7,1,   1,NULL
UNION ALL SELECT 8,5,   0,NULL
),LevelHire AS
(
        SELECT EmpId, ManagerId,Level,Value
        FROM EmployeeTable
        WHERE EmpId = 2
        UNION ALL
        SELECT RC.EmpId,  RC.ManagerId, Lh.Level,LH.Value
        FROM LevelHire LH
        INNER JOIN [EmployeeTable] RC
        ON LH.EmpId= RC.ManagerId
)
SELECT  E.EmpId, E.ManagerId,E.Level,ISNULL(E.Value ,LH.Value) Value
FROM EmployeeTable E
    LEFT JOIN LevelHire LH
    ON E.EmpId = LH.EmpId

这篇关于使用 CTE 将所有员工置于经理之下的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！