Oracle递归查询(start with…connect by prior)
查询基本结构:
select … from table_name
start with 条件1
connect by 条件2
1、建测试用表
1 create table test_prior( 2 ids number, 3 son varchar2(200), 4 father varchar2(200) 5 );
并插入数据
start with指定树的根(即父节点)实际上你还可以指定多个根的,比如 father in ('爷爷', '爸爸')
而connect by prior son = father相当于表名在递归的过程中,查找到的树中其它节点接着又作为根结点。然后继续递归(prior在的一侧表示该侧为子节点)
为了便于理解可以可以connect by prior那一行当作多余的,只记住要查询的列名放前面,根列名放等号后面就行,这样只要指定树的根结点就比较好理解了。
1 select son 2 from test_prior 3 start with father = '爷爷' 4 connect by prior son = father; 5 6 select distinct son 7 from test_prior 8 start with father in( '爷爷' , '爸爸' )--指定多个根会出现重复的数据 9 connect by prior son = father;
2、START WITH 可以省略,比如
1 select son from test_prior connect by prior son = father;
此时不指定树的根的话,就默认把test_prior整个表中的数据从头到尾遍历一次,每一个数据做一次根,然后遍历树中其他节点信息.
在这个例子中,上面的SQL等价如下:
1 select son 2 from test_prior 3 start with father in ('爷爷', '爸爸', '儿子', '孙子A', '孙子B', '孙子C') 4 connect by prior son = father;
那查询到的结果如下,有很多重复信息的
3、nocycle关键字
我们知道标准的树结构中是不会有环的,但表中的树形结构不是标准的,有可能导致环的出现,当然在Oracle中的role是禁止出现循环的.比如你grant A to B ,grant B to C 。再来个grant C to A会出错的
假如有个上面的环,在再使用开始的递归查询语言会出错.得用nocycle关键字指定忽略环。
1 select son 2 from test_prior 3 start with father = '爷爷' 4 connect by nocycle prior son = father;
其他特性:
1、level关键字,代表树形结构中的层级编号;第一层是数字1,第二层数字2,依次递增。
2、CONNECT_BY_ROOT方法,能够获取第一层集结点结果集中的任意字段的值;例CONNECT_BY_ROOT(字段名)。
1 select t.*,level,son,father,connect_by_root(son) 2 from test_prior t 3 start with father = '爷爷' 4 connect by prior son = father
1 select t.*,level,son,father,connect_by_root(father) 2 from test_prior t 3 start with father = '爷爷' 4 connect by prior son = father
3、实现1到10的序列。
使用rownum或level实现1到10的序列
1 select rownum from dual connect by rownum<=10; 2 select level from dual connect by level<=10;
例:查询当前时间往前的12周的开始时间、结束时间、第多少周
1 select sysdate - (to_number(to_char(sysdate - 1, 'd')) - 1) - 2 (rownum - 1) * 7 as startDate, 3 sysdate + (7 - to_number(to_char(sysdate - 1, 'd'))) - 4 (rownum - 1) * 7 as endDate, 5 to_number(to_char(sysdate, 'iw')) - rownum + 1 as weekIndex 6 from dual 7 connect by level<= 12;--将level改成rownum可以实现同样的效果
(注:此为学习记录笔记,仅供参考若有问题请指正,后续补充......)
参考原文:https://blog.csdn.net/wang_yunj/article/details/51040029
参考原文:https://blog.csdn.net/weiwenhp/article/details/8218091