CREATE TABLE Employee
(
UserID INT,
ManagerID INT,
Name NVARCHAR(10)
)
INSERT INTO dbo.Employee
SELECT 1,-1,N'Boss'
UNION ALL
SELECT 11,1,N'A1'
UNION ALL
SELECT 12,1,N'A2'
UNION ALL
SELECT 13,1,N'A3'
UNION ALL
SELECT 111,11,N'B1'
UNION ALL
SELECT 112,11,N'B2'
UNION ALL
SELECT 121,12,N'C1'
UNION ALL
SELECT 1211,121,N'CC1'
WITH CTE AS(
SELECT UserID,ManagerID,Name,Name AS ManagerName
FROM dbo.Employee
WHERE ManagerID=-1
UNION ALL
SELECT c.UserID,c.ManagerID,c.Name,p.Name AS ManagerName
FROM CTE P
INNER JOIN dbo.Employee c ON p.UserID=c.ManagerID
)
SELECT UserID,ManagerID,Name,ManagerName
FROM CTE
WITH CTE1 AS(
SELECT UserID,ManagerID,Name,CAST(Name AS NVARCHAR(MAX)) AS LPath
FROM dbo.Employee
WHERE ManagerID=-1
UNION ALL
SELECT c.UserID,c.ManagerID,c.Name,p.LPath+'->'+c.Name AS LPath
FROM CTE1 P
INNER JOIN dbo.Employee c ON p.UserID=c.ManagerID
)
SELECT UserID,ManagerID,Name,LPath
FROM CTE1
sql递归
最新推荐文章于 2024-08-20 11:18:45 发布