我有一张有员工详细信息的表格
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
