欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle 递归查询

程序员文章站 2022-05-12 18:32:32
...
递归查询基础结构
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;





相关标签: Oracle 递归