with cte as
(
select [DeptID],[MasterID],[DeptName],1 as lvl from Department
where [DeptID] = 55
union all
select d.[DeptID],d.[MasterID],d.[DeptName],lvl+1 from cte c inner join Department d
on c.[DeptID] = d.[MasterID]
)
select * from cte
结果:
DeptID MasterID DeptName lvl
55 0 上级领导 1
56 55 司法部检查组 2