查询树状结构某节点下的所有子节点
with cte_child(id,areaName,pid,level)
as
(
--起始条件
select id,areaName,pid,0 as level from erp_area
where id = 1 -- 优先列出第一节点查询条件
union all
--递归条件
select a.id,a.areaName,a.pid,b.level+1
from erp_area a
inner join
cte_child b
on ( a.pid=b.id)
)
select * from cte_child
查询树状结构某节点(44)的上级所有根节点
with cte_child(id,areaName,pid)
as
(
--起始条件
select id,areaName,pid from erp_area
where id = 44 --列出子节点查询条件
union all
--递归条件
select a.id,a.areaName,a.pid
from erp_area a
inner join
cte_child b --执行递归
on a.id=b.pid
)
select * from cte_child;
原文:https://www.cnblogs.com/liguang/p/12366115.html
如果您也喜欢它,动动您的小指点个赞吧