How to find level of employee position using RECURSIVE COMMON TABLE EXPRESSION

    ;with Ranked(Empid,Mngrid,Empnm,RN,level) As
    (select Empid,Mngrid ,Empnm ,row_number() over (order by Empid)AS RN , 
     0 as level from     dbo.EmpMngr),
     AnchorRanked(Empid,Mngrid,Empnm,RN,level)
     AS(select Empid,Mngrid,Empnm,RN ,level from Ranked ),
     RecurRanked(Empid,Mngrid,Empnm,RN,level)
     AS(select Empid,Mngrid,Empnm,RN,level from AnchorRanked 
     Union All
    select Ranked.Empid,Ranked.Mngrid,Ranked.Empnm,Ranked.RN,Ranked.level + 1 
     from  Ranked 
     inner join RecurRanked 
       on Ranked.Empid = RecurRanked.Empid AND 
         Ranked.RN = RecurRanked.RN+1)

se开发者_JAVA技巧lect Empid,Empnm,level from RecurRanked

This may help CTE Example of a simple hierarchy

;WITH OrgChart AS 
(
   SELECT EmpID, EmpNm, [Level] = 0
   FROM EmpMngr
   WHERE MngrID IS NULL
   UNION ALL
   SELECT e.EmpID, e.EmpNm, [Level] + 1
   FROM OrgChart o
   JOIN EmpMngr e ON (e.MngrID = o.EmpID) 
)
SELECT EmpID, EmpNm, [Level] FROM OrgChart