sql Server实现递归查询示例有？

----------sql server 递归查询----------
--查找上级所有节点
with uCte as
(
 select a.id,a.title,a.pid from tree_table a where id = 3--当前节点
 union all
 select k.id,k.title,k.pid from tree_table k
 inner join uCte c on c.pid = k.id
)
select * from uCte;
--查找上级所有节点
with dCte as
(
 select a.id,a.title,a.pid from tree_table a where id = 3--当前节点
 union all
 select k.id,k.title,k.pid from tree_table k
 inner join dCte c on c.id = k.pid
)
select * from dCte;