Oracle 递归查询

[b]递归查询基础结构[/b]

select … from tablename
start with 条件1
connect by 条件2
where 条件3;

[b]按父节点查询所有子孙节点[/b]

select * from br_category m start with m.category_id='1' connect by m.parent_id=prior m.category_id;

[b]按子节点找到所有父辈节点[/b]

select * from br_category m start with m.category_id='CC00000051' connect by prior m.parent_id=m.category_id;

[b]查找一个节点的直属父节点[/b]

select c.category_id, c.name, p.category_id parent_id, p.name
from br_category c, br_category p
where c.parent_id=p.category_id and c.category_id='CC00000001'

[b]获取层级及是否是叶子节点[/b]

SELECT   category_id,   NAME,   PARENT_ID,   LEVEL,   CONNECT_BY_ISLEAF
FROM     br_category
         START WITH category_id='1'
         CONNECT BY PRIOR category_id=PARENT_ID 
         ORDER BY category_id;