Question

如何利用SQL Server With As递归获取层级关系数据

Answer 1

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
FROM dbo.MyEmployees
WHERE ManagerID IS NULL
UNION ALL --主要是在此，增加一个union all
SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d -- 在此调用自身，就可以实现递归了
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports
ORDER BY ManagerID;

Answer 2

WITH Emp
AS ( SELECT ID ,
EName ,
ParentGUID
FROM dbo.Employee
WHERE ID = '5C8214EC-258B-4C44-9F31-206E499F0285'
UNION ALL
SELECT d.ID ,
d.EName ,
d.ParentGUID
FROM Emp
INNER JOIN dbo.Employee d ON d.ID = Emp.ParentGUID
)
SELECT ID ,
EName
FROM Emp