对于oracle进行简单树查询(递归查询),很早想总结了,发现了一个,转过来DEPTID PAREDEPTID NAME
NUMBER NUMBER CHAR (40 Byte)
部门id 父部门id(所属部门id) 部门名称
通过子节点向根节点追朔.
Sql代码
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
通过根节点遍历子节点.
Sql代码
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
可通过level 关键字查询所在层次.
Sql代码
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
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 后面所放的字段是有关系的,它指明了查询的方向。
练习: 通过子节点获得顶节点
Sql代码
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
查询从某个时间开始到今天最后一秒每隔20分钟的时间:
select to_char(to_date('200906121700', 'yyyymmddhh24mi') + 20 * level / (24 * 60),'yyyymmddhh24mi') stime
from dual
connect by level <= (3 * 24 * 60) / 20
要求:递归查询根节点下的所有叶子节点
1、根据根节点id查询叶子
select distinct *
from three o where CONNECT_BY_ISLEAF = 1
start with o.three_id = 654321 connect by prior o.three_ID =o.PARENT_ID
o.three_id 是开始查询的根节点id
o.PARENT_ID 是父节点id
2.查询相同样板下的所有根节点下的叶子节点
select distinct *
from three o where CONNECT_BY_ISLEAF = 1 and o.type_id =123456
start with o.parent_id = 0 connect by prior o.three_ID =o.PARENT_ID
o.type_id 是样板id
o.parent_id 是父节点id,当节点是根节点时,父节点id等于0
start with 代表开始查询的id值
connect by prior 递归的条件,如果从叶子节点递归根节点,则以上例子的o.three_ID =o.PARENT_ID 互换即可
在 Oracle 10g 中,可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”,如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示“0”。