Oracle递归查询connectby用法(2)-木庄网络博客

返回值: 0表示不是叶子节点， 1表示是叶子节点

例：

4、CONNECT_BY_ROOT 字段x -> 找到该节点最顶端节点的字段x

select last_name "Employee", connect_by_root last_name "Manager",sys_connect_by_path(last_name, ' -> ') "Path" from hr.employees
where level > 1
connect by prior employee_id = manager_id
order by last_name, length("Path");

思考? 为什么不能加 start with ? 加了会有什么效果？

不加start with , 则每个节点都遍历一次 , connect_by_root 找到顶端的经理人会不同

而加了start with manager_id is null 则从树的根节点 King 开始遍历, 从而connect_by_root每个人的顶端的经理都是King

5、10g新特性采用sibilings排序

作用: 因为使用order by排序会破坏层次，所以在oracle10g中，增加了siblings关键字的排序给叶子节点的关键字排序。

语法：

order siblings by <expre> asc|desc ;

它会保护层次，并且在每个等级中按expre排序

注意: order siblings by 必须紧跟着connect by，所以不能再用order by 了

例子：用order by，最后的结果是严格按照salary排序的,这样把层级关系都打乱了

select t.employee_id,t.manager_id,t.first_name,t.salary, sys_connect_by_path(t.first_name, '->'), level from hr.employees t
 start with manager_id is null
 connect by prior employee_id = manager_id
 order by salary desc;

采用sibilings排序:结果的树结构没有被打乱,且没层级的sibilings都是按照salary排序的。

select t.employee_id,t.manager_id,t.first_name,t.salary,sys_connect_by_path(t.first_name, '->'),level from hr.employees t
 start with manager_id is null
 connect by prior employee_id = manager_id
 order siblings by salary desc;

三、与row num 生成序列记录

rownum可用level代替。

1、简单序列：

select rownum from dual connect by rownum<=4

1
2
3
4

2、生成10-14的连续数（10开始，5行数据）

select 10+(rownum-1) from dual connect by rownum<=14-10+1

3、生成a-d的四个字母

select chr(ascii('a')+(rownum-1)) from dual connect by rownum<=ascii('d')-ascii('a')+1

4、生成2011-01-05至2011-01-10的日期

select to_date('2011-01-05','yyyy-mm-dd')+(rownum-1) from dual connect by rownum<=to_date('2011-01-10','yyyy-mm-dd')-to_date('2011-01-05','yyyy-mm-dd')+1

查询当前时间往前的12周的开始时间、结束时间、第多少周

select sysdate - (to_number(to_char(sysdate - 1, 'd')) - 1) - (rownum - 1) * 7 as startDate,
       sysdate + (7 - to_number(to_char(sysdate - 1, 'd'))) - (rownum - 1) * 7 as endDate,
       to_number(to_char(sysdate, 'iw')) - rownum + 1 as weekIndex
  from dual
connect by level<= 12;--将level改成rownum可以实现同样的效果

d 表示一星期中的第几天
iw 表示一年中的第几周

5、字符串分割，由一行变为多行。

生成a1,b1,d1序列

select substr(id,
        instr(id,',',1,rownum)+1,
        instr(id,',',1,rownum+1) - instr(id,',',1,rownum)-1)--根据逗号的位置进行拆分
from (select ','||'a1,b1,d1'||',' as id from dual) --前后各加一个逗号
connect by rownum<=length(id)-length(replace(id,',',''))-1

或者

select REGEXP_SUBSTR('a1,b1,d1', '[^,]+', 1, rownum) as newport 
    from dual connect by rownum <= REGEXP_COUNT('a1,b1,d1', '[^,]+');

6、利用with子句生成测试数据

with temp as
(select 'a' as A,'b' as B from dual 
  union
  select 'c' as C,'d' as D from dual 
)
  select * from temp;

7、日期维度数据生成方法。

select  to_date('2011-01-05','yyyy-mm-dd')+(rownum-1)  as ydate_date,
  to_char(to_date('2011-01-05','yyyy-mm-dd')+(rownum-1),'yyyy') as ydate_month
 from dba.tab_cols where  to_date('2011-01-05','yyyy-mm-dd')+(rownum-1) != to_date('2060-01-05','yyyy-mm-dd')

到此这篇关于Oracle递归查询connect by的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持。

标签：Oracle

返回前面的内容