首页 > 代码库 > Oracle 树操作

Oracle 树操作

 select…start with…connect by…prior

1.查找一个节点的所有直属子节点(节点本身+所有后代),有以下两种写法。

select id, typename, typecode, parentid, ordernumfrom purchase_typewhere status = 1  start with id = 15  connect by parentid = prior id
  order siblings by ordernum

select id, typename, typecode, parentid, ordernumfrom purchase_typewhere status = 1  start with id = 15  connect by prior id = parentid  order siblings by ordernum

显示结果:

技术分享

2.查找一个节点的所有直属父节点(节点本身+所有祖宗(父类的父类....))。

select id, typename, typecode, parentid, ordernum
from purchase_type
where status = 1
  start with id = ‘136‘
  connect by id = prior parentid
  order siblings by ordernum

select id, typename, typecode, parentid, ordernumfrom purchase_typewhere status = 1    start with id = 136    connect by prior parentid = id    order siblings by ordernum

显示结果:

技术分享

Oracle 树操作