Oracle 递归查询 博客分类： Database Oracle递归

递归查询基础结构

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

按父节点查询所有子孙节点

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

按子节点找到所有父辈节点

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

查找一个节点的直属父节点

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'

获取层级及是否是叶子节点

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;