sql递归查询~

------------------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