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
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
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”。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/559237/viewspace-604465/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/559237/viewspace-604465/