oracle树节点查询,查询树节点、oracle、select...start with...connect by prior...

通过子节点向根节点追朔.

select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid

通过根节点遍历子节点(不包含根节点).

select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid

通过根节点遍历子节点(包含根节点).

select * from persons.dept start with deptid=0 connect by prior deptid=paredeptid

可通过level 关键字查询所在层次.

select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid

备注：

start with ...connect by 的用法， start with 后面所跟的就是就是递归的种子。

递归的种子也就是递归开始的地方 connect by 后面的"prior" 如果缺省：则只能查询到符合条件的起始行，并不进行递归查询；

connect by prior 后面所放的字段是有关系的，它指明了查询的方向。

练习： 通过子节点获得顶节点

select FIRST_VALUE(deptid) OVER (ORDER BY LEVEL DESC ROWS UNBOUNDED PRECEDING) AS firstdeptid from persons.dept start with deptid=76 connect by prior paredeptid=deptid

表结构

DEPTID PAREDEPTID NAME

NUMBER NUMBER CHAR (40 Byte)

部门id 父部门id(所属部门id) 部门名称

oracle树操作(select .. start with .. connect by .. prior)

oracle中的递归查询能够使用:select .. start with .. connect by .. prior 以下将会讲述oracle中树形查询的经常使用方式.仅仅涉及到一张表. star ...

Oracle 树操作(select…start with…connect by…prior)

摘自:http://www.cnblogs.com/linjiqin/archive/2013/06/24/3152674.html oracle树查询的最重要的就是select…start with ...

[转]Oracle 树操作(select…start with…connect by…prior)

转自http://www.cnblogs.com/linjiqin/archive/2013/06/24/3152674.html Oracle 树操作(select-start with-conne ...

oracle树操作(select start with connect by prior)

oracle中的递归查询可以使用:select .. start with .. connect by .. prior 下面将会讲述oracle中树形查询的常用方式,只涉及到一张表. 一. 建表语句 ...

Oracle学习之Oracle 树操作(select…start with…connect by…prior)

转自:http://www.cnblogs.com/linjiqin/archive/2013/06/24/3152674.html oracle树查询的最重要的就是select…start with ...

Oracle 树操作、递归查询(select…start with…connect by…prior)

一.Oracle中start with…connect by prior子句用法 connect by 是结构化查询中用到的,其基本语法是:select … from tablename start ...

(转载)Oracle 树操作(select…start with…connect by…prior)

转载地址:https://www.cnblogs.com/linjiqin/p/3152674.html 备注:如有侵权,请立即联系删除. oracle树查询的最重要的就是select…start w ...

[转]Oracle 树操作(select…start with…connect by…prior)