无限循环的原因是 empid=mgrid 的第一条记录.为了处理这个问题，你应该包含一个累积字段(在这个例子中为 levels)来存储你已经处理过的 mgrid 并检查 emid 是否已经存在在这个列表中以避免循环.

The reason of an infinite loop is the first record where empid=mgrid. To handle this issue you should include a cumulative field (levels in this example) to store mgrid you have already processed and check if emid is already in this list to avoid a loop.

这是一个查询:

with Tree as
        (
        SELECT  empid
        ,       mgrid
        ,       1 as lv
        ,       1 as level1
        ,       null as level2
        ,       null as level3
        ,       null as level4
        ,       null as level5
        ,       cast(mgrid as varchar(max)) levels  
        FROM    Employees
        WHERE   empid = 1 and mgrid = 1
        UNION ALL
        SELECT  E.empid
        ,       E.mgrid
        ,       T.lv + 1
        ,       T.level1
        ,       case when T.lv = 1 then E.empid else t.level2 end
        ,       case when T.lv = 2 then E.empid else t.level3 end
        ,       case when T.lv = 3 then E.empid else t.level4 end
        ,       case when T.lv = 4 then E.empid else t.level5 end
        ,       T.levels+','+cast(E.mgrid as varchar(max)) levels   

          FROM    Employees AS E
        JOIN    Tree T
        ON      E.mgrid = T.empid 
                and (','+T.levels+',' 
                      not like 
                     '%,'+cast(E.empid as varchar(max))+',%')
        )
select  *
from Tree
order by empid

这里是 SQLFiddle 演示