DECLARE
@ParentID int =2, @Level int = 3
;WITH CTE AS
(
SELECT 0 LEVEL, T.EMPLOYEE,T.SUPERVISOR
FROM STAFF T
WHERE T.EMPLOYEE =@ParentID
UNION ALL
SELECT B.LEVEL+1,A.EMPLOYEE,A.SUPERVISOR
FROM STAFF A
JOIN CTE B
ON A.SUPERVISOR=B.EMPLOYEE
WHERE A.EMPLOYEE<>A.SUPERVISOR
)
SELECT *
FROM CTE
WHERE LEVEL = @Level;
Selina-19 2017-09-20 18:24:06
DECLARE @ID int=13
;WITH CTE AS
(SELECT 0 LEVEL, T.EMPLOYEE,T.SUPERVISOR
FROM STAFF T
WHERE T.EMPLOYEE =@ID
UNION ALL
SELECT B.LEVEL+1,A.EMPLOYEE,A.SUPERVISOR
FROM STAFF A
JOIN CTE B
ON A.EMPLOYEE=B.SUPERVISOR
WHERE A.EMPLOYEE<>A.SUPERVISOR)
SELECT * FROM CTE
SELECT * FROM STAFF;