SQL Server 递归查询带层级缩进展示

SQL Server 递归查询（层级缩进展示）

--查询1：完整树
with cte
as
(
select ID,Name,ParentID,0 as Lev,cast(ID as varbinary) as Sort
from DicTree where ParentID=0
union all
select dt.ID,dt.Name,dt.ParentID,Lev+1,cast(c.Sort+cast(dt.ID as varbinary) as varbinary) as Sort
from cte as c
join DicTree as dt on dt.ParentID=c.ID
)
select *,SPACE(Lev * 2) + Name AS LevName from cte order by Sort


--查询2：根据传入的ID，展示当前ID树
with cte
as
(
select ID,Name,ParentID,0 as Lev,cast(ID as varbinary) as Sort
from DicTree where ID=1
union all
select dt.ID,dt.Name,dt.ParentID,Lev+1,cast(c.Sort+cast(dt.ID as varbinary) as varbinary) as Sort
from cte as c
join DicTree as dt on dt.ParentID=c.ID
)
select *,SPACE(Lev * 2) + Name AS LevName from cte order by Sort

表结构及查询结果如下：