使用CTE进行递归查询，能够实现对层次结构的数据的快速访问，非常有用。
1，CTE的递归结构
递归查询的结构包括两部分：起始点和迭代公式。
使用CTE递归，其迭代终止条件是达到max_recursive指定的递归次数，或递归公式返回的结果集是empty，（即递归公式没有返回任何结果）。
创建测试数据：ManagerID是UserID的父节点，这是一个非常简单的层次结构模型。
use tempdb
go
create table dbo.dt_user
(
UserID int,
ManagerID int,
Name )
)
insert into dbo.dt_user
,,N'Boss'
union all
,,N'A1'
union all
,,N'A2'
union all
,,N'A3'
union all
,,N'B1'
union all
,,N'B2'
union all
,,N'C1'
2，查询每个User的的直接上级Manager。
;with cte as
(
select UserID,ManagerID,name,name as ManagerName
from dbo.dt_user
union all
select c.UserID,c.ManagerID,c.Name,p.name as ManagerName
from cte P
inner join dbo.dt_user c
on p.UserID=c.ManagerID
)
select UserID,ManagerID,Name,ManagerName
from cte
order by UserID
step1：查询ManagerID=-1，作为root node，这是递归查询的起始点。
step2：迭代公式是 union all 下面的查询语句。在查询语句中调用中cte，而查询语句就是cte的组成部分，即 “自己调用自己”，这就是递归的真谛所在。
所谓迭代，是指每一次递归都要调用上一次查询的结果集，Union ALL是指每次都把结果集并在一起。
step3-N，迭代公式利用上一次查询返回的结果集执行特定的查询，直到CTE返回null 或达到最大的迭代次数，默认值是32。最终的结果集是迭代公式返回的各个结果集的并集，求并集是由Union All 子句定义的，并且只能使用Union ALL。
3，查询路径，在层次结构中查询子节点到父节点的path
;with cte as
(
select UserID,ManagerID,name,cast(name as nvarchar(max)) as ReportPath
from dbo.dt_user
union all
select c.UserID,c.ManagerID,c.Name,c.name+'->'+p.ReportPath as ReportPath
from cte P
inner join dbo.dt_user c
on p.UserID=c.ManagerID
)
select UserID,ManagerID,Name,ReportPath
from cte
order by UserID
参考doc：