sql-server – 行使CTE获取司理下的全部员工

我有一张有员工具体信息的表格

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

此刻,我必需从员工身份2开始,发明全部人都是初级此外雇员(即2,4,5,6,8),并将其分派给与“2”沟通的值(即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)

我奈何才气实现同样的方针？

办理要领

;WITH EmployeeTable AS 
(
SELECT 1 EmpId,0 ManagerId,5 Level,'CEO' Value
UNION ALL SELECT 2,1,'EMP'
UNION ALL SELECT 3,'ORG'
UNION ALL SELECT 4,2,3,NULL
UNION ALL SELECT 5,NULL
UNION ALL SELECT 6,NULL
UNION ALL SELECT 7,NULL
UNION ALL SELECT 8,NULL
),LevelHire AS
(
        SELECT EmpId,Level,Value
        FROM EmployeeTable
        WHERE EmpId = 2
        UNION ALL
        SELECT RC.EmpId,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

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!