WITH tree as(
SELECT DEP,NAME,UP,
LEV=0 -- 定义层级字段,并且设置顶级的值
FROM DEPT
where dep='00000000' -- 顶级数据条件,查根目录
UNION ALL
SELECT b.DEP,b.NAME E,B.UP,LEV=tree.LEV+1 -- 递归中每下去一级层级就+1
from tree
inner join dept b on tree.dep=b.up
)
SELECT *
FROM tree
order by tree.lev
OPTION (MAXRECURSION 0); -- 该处设置最大递归次数,默认是100,设置为0则递归次数不限