------------------sql父节点找子节点-----------------------------
with cte(id,parentid,name)
as
(--父项
select id,parentid,name from db_owner.Sys_FunctionPoint where parentid = 0
union all
--递归结果集中的下级
select t.id,t.parentid,t.name from db_owner.Sys_FunctionPoint as t
inner join cte as c on t.parentid = c.id
)
select id,parentid,name from cte
------------------sql子节点找父节点-----------------------------
--由子级递归父项
with cte(id,parentid,name)
as
(--下级父项
select id,parentid,name from db_owner.Sys_FunctionPoint where id = 20
union all
--递归结果集中的父项
select t.id,t.parentid,t.name from db_owner.Sys_FunctionPoint as t
inner join cte as c on t.id = c.parentid
)
select id,parentid,name from cte
---------------------------oracle-----------------------------
Oracle版本的Sql如下:
比如一个表,有id和pId字段,id是主键,pid表示它的上级节点,表结构和数据请参考SqlServer2005的,Sql如下:
--下面的Sql是查询出1结点的所有子结点
SELECT * FROM aaa
START WITH id = 1
CONNECT BY pid = PRIOR id
--下面的Sql是查询出8结点的所有父结点
SELECT * FROM aaa
START WITH id = 8
CONNECT BY PRIOR pid = id