WITH EmpsCTE AS
(
SELECT EmployeeID, ReportsTo, FirstName, LastName
FROM dbo.Employees
WHERE EmployeeID = 2
UNION ALL
SELECT EMP.EmployeeID, EMP.ReportsTo, EMP.FirstName, EMP.LastName
FROM EmpsCTE AS MGR
JOIN dbo.Employees AS EMP
ON EMP.ReportsTo = MGR.EmployeeID
)
SELECT * FROM EmpsCTE;
这是我刚从书上看到的。希望对你有帮助,默认为100次递规,如果不限制的话可以SELECT * FROM EmpsCTE OPTION(MAXRECURSION 0)
这里只建立了一个表,另要建立覆盖索引,
CREATE UNIQUE INDEX idx_mgr_emp_ifname_ilname
ON dbo.Employees(ReportsTo, EmployeeID)
INCLUDE(FirstName, LastName);
你可以根据你的具体情况修改下.