Oracle connect by与level的使用_connect by level

1.level是什么？

level是在树形结构中、表示层级的伪列。

1.1树形结构

Oracle支持树形结构的查询，用关键字connect by 表示。
connect by表示在构造树形结构时，上下级的确定方式或者用来查找下（上）级记录的条件，满足这个条件就拼到结果树里。

默认是从根开始，如connect by prior id=pid，表示构造树时，本记录的id是下条的pid，即找pid=本条id的记录做下条记录。
交换prior位置，表示从叶开始。如connect by id= prior pid，表示构造树时，本记录的pid是下条的id。
ps：prior表示构造树的源头方向。

1.2 如何生成

因为level只在树形结构中，所以level必须与connect by 结对出现。
实质是递归方法。

1. 有正常上下级结构
   如connect by prior id=pid，从本表查找符合pid是本条id的记录，递归执行。
2. 无上下级结构
   假设有1个表tab，只有1个字段col，含2条记录。

如connect by level<=3，是没有制定父子id关系，只要查找level<=3的记录。因为本表所有记录的level都是1，所以每个层级、每个节点都会取全部记录构造。
会形成：
第一层：1,2
第二层：1-1,1-2,2-1,2-2（递归构造第2层时，本表只有level1，满足条件，仍然找到全部记录）
第三层：1-1-1,1-1-2,1-2-1,1-2-2,2-1-1,2-1-2,2-2-1,2-2-2
构造的元素个数公式：n=个数；m=层级；=n+n²⁺ⁿ3+…+n^m
如2个数、3层，构造的元素数：=2+2*2+2*2*3=13个；
如6个数、2层，构造的元素数：=6+6*6=42个。

2.使用场景

1.构造连续的数字

select level from dual connect by level<=5;
1

原理说明：
1.dual只有1条记录，所以构造树时，每层都只有1个记录，level不会重复；
2.构造条件是：level<=5，所以截止到5。

2.构造连续的日期

由场景1的推演，可以是日期、月份等，步长也可以调整。

select sysdate+level from dual connect by level<=5;
1

3.拆解字符串

是树查询与正则表达式的完美结合。
如，要把[a,b,22]按逗号拆解开。

SELECT REGEXP_SUBSTR('a,b,22', '[^,]+', 1, LEVEL)
  FROM DUAL
CONNECT BY REGEXP_SUBSTR('a,b,22', '[^,]+', 1, LEVEL) IS NOT NULL
1
2
3

原理说明：
1.dual只有1条记录，所以构造树时，每层都只有1个记录，可能形成了包含本记录的n次不同level的重复。
2.connect by条件：按层级截取表达不能为空，因为只有3个元素，所以只到3级。
3.正则表达式的含义：

1. pattern=‘[^,]+’，[]表待选集合，但以^开头，表示非逗号的所有元素。
2. 是从左开始截取、直到遇到不符合pattern的元素。

4.拆解区间

如图，把区间表达式1-5，拆解成1,2,3,4,5。

with temp0 as
 (select t.range_num,
         REGEXP_SUBSTR(t.range_num, '[^-]+', 1, 1) minNum, --最小num
         REGEXP_SUBSTR(t.range_num, '[^-]+', 1, 2) maxNum --最大num
    from (select '1-5' as range_num
            from dual
          union
          select '6-9' as range_num
            from dual) t)

select t1.range_num, t2.lv
  from temp0 t1
  join (select level lv
          from dual
        CONNECT BY LEVEL <= (select max(maxNum) from temp0)) t2
    on (t2.lv >= t1.minNum and t2.lv <= t1.maxNum);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

原理说明：
1.用正则表达式，拆解出范围的最大、最小值。如1-5对应1,5；
2.用connect by 构造范围对应的集合。本例最大值是9，顾是1,2,3…9
3.用表连接构造最终结果。连接条件：t2.lv >= t1.minNum and t2.lv <= t1.maxNum。