基本语法
在oracle中start with connect by (prior) 用来对树形结构的数据进行查询。其中start with conditon 给出的是数据搜索范围, connect by后面给出了递归查询的条件;
涉及的伪列及函数:
– connect_by_isleaf 伪列
– connect_by_root()获取根节点
– sys_connect_by_path(a,b) 显示分层路径
– connect_by_iscycle 是否循环
语法:
[start with]
connect by [nocycle] prior 条件
[and]
select * from table [start with condition1] connect by [prior] id=parentid
一般用来查找存在父子关系的数据，也就是树形结构的数据；其返还的数据也能够明确的区分出每一层的数据。start with condition1 是用来限制第一层的数据，或者叫根节点数据；以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据以此类推。connect by [prior] id=parentid 这部分是用来指明oracle在查找数据时以怎样的一种关系去查找；比如说查找第二层的数据时用第一层数据的id去跟表里面记录的parentid字段进行匹配，如果这个条件成立那么查找出来的数据就是第二层数据，同理查找第三层第四层…等等都是按这样去匹配。
select * from table [start with condition1] connect by id= [prior] parentid
这种用法就表示从下往上查找数据，可以理解为从叶子节点往上查找父级几点，用第一层数据的parentid去跟表记录里面的id进行匹配，匹配成功那么查找出来的就是第二层数据；上面的那种就是从父级节点往下查找叶子节点。
其他特性
level关键字，代表树形结构中的层级编号；第一层是数字1，第二层数字2，依次递增。
CONNECT_BY_ROOT方法，能够获取第一层集结点结果集中的任意字段的值；例CONNECT_BY_ROOT（字段名）。
- start with ：设置起点，省略后默认以全部行为起点。
- connect by [condition] ：与一般的条件一样作用于当前列，但是在满足条件后，会以全部列作为下一层级递归（没有其他条件的话）。
- prior ： 表示上一层级的标识符。经常用来对下一层级的数据进行限制。不可以接伪列。
- level ：伪列，表示当前深度。
- connect_by_root() ：显示根节点列。经常用来分组。
- connect_by_isleaf ：1是叶子节点，0不是叶子节点。在制作树状表格时必用关键字。
- sys_connect_by_path() ：将递归过程中的列进行拼接。
- nocycle , connect_by_iscycle : 在有循环结构的查询中使用。
- siblings : 保留树状结构，对兄弟节点进行排序
二、典型用法
1、生成数字序列结果集
select rownum from dual connect by rownum<=10;
select level from dual connect by level<=10;
2、查询当前时间往前的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 表示一年中的第几周
3、字符串分割，由一行变为多行
比如说分割01#02#03#04这种有规律的字符串
select REGEXP_SUBSTR('01#02#03#04', '[^#]+', 1, rownum) as newport
from dual
connect by rownum <= REGEXP_COUNT('01#02#03#04', '[^#]+');