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.*,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可以实现同样的效果

（Oracle学习笔记记录20181121）

参考原文：https://blog.csdn.net/wang_yunj/article/details/51040029

参考原文：https://blog.csdn.net/weiwenhp/article/details/8218091

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!