Oracle connect by level

转于 ： https://blog.csdn.net/zwjzqqb/article/details/79066224 感谢博主

 

1. 准备测试用表

CREATE TABLE T  (ID VARCHAR2(10));
INSERT INTO T VALUES ('A');
INSERT INTO T VALUES ('B');
INSERT INTO T VALUES ('C');
COMMIT;
select * from t;
-- 准备一张一列三行的测试表

    1
    2
    3
    4
    5
    6
    7

2. 简单测试和解释

select id,level from t connect by level<1;

    1

    伪列level必须和connect by 一起使用
    本例讨论的是 connect by level < h，而非 connect by level< = h（实际上就是相差1的区别）
    有些时候伪列level和rownum可以做替换使用
    该sql会生成树形结构
    t表中有N条数据，则生成N个子树
    每个子树有 h-1 层，即高度为 L=h-1，查出的数据中level的值最大为 h-1
    sql查出来的记录顺序是生成的子树以先根遍历的顺序
    当h为1或者2时，子树的高度都为1（因为没有高度为0的树），SQL查询结果就是t表的所有记录

3. level大于3时的测试和解释

select id,level from t connect by level<3;
-- 表t有3条记录，N=3
-- 条件 level<3，L=2
-- 那么会生成3棵高度为2的子树，然后先根遍历

    1
    2
    3
    4

    该SQL实际上生成了上图所示的三棵子树
    先根遍历的结果就是SQL查出来的记录顺序

4. level大于4时的测试和解释

select id,level from t connect by level<4;
-- 表t有3条记录，N=3
-- 条件 level<4，L=3
-- 那么会生成3棵高度为3的子树，然后先根遍历

    1
    2
    3
    4

5. 衍生出来的相关技巧

select level from dual connect by level<=5;
select level from dual connect by rownum<=5;
select rownum from dual connect by rownum<=5;
select rownum from dual connect by level<=5;
-- 有些时候伪列level和rownum可以做替换使用
-- 但并不是level和rownum完全一样
select id, rownum, level from t connect by level < 5;
select id, rownum, level from t connect by rownum < 5;
-- 当表不是一行一列的dual时，是要慎用两者的替换的
-- 这俩条SQL结果不在截图展示，请自行测试

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

6. 技巧继续衍生

select * from t,(select level from dual connect by level<=5);
-- 使用笛卡尔积，将原表复制出N份记录

    1
    2

insert into t select * from t;
commit;
-- 相似的制造数据的方法
-- 重复几次你的表就会爆炸掉
-- BOOOOOM！

    1
    2
    3
    4
    5

7. 技巧再次衍生

该技巧的再次衍生，可以用于case列传行的还原SQL，因为case的行列转换及其还原系列博文还未发布，请暂时参照《Oracle regexp_substr函数简摘》中的样例3的推演1和推演2，该博文的样例3的推演实际上就是
case列传行的还原SQL的推演。
8. 衍生出来的SQL炸弹

select level lvl
  from (select level lvl from dual connect by level <= n)
connect by level <= n;
-- 当n为5的时候记录数为 3905
-- 当n为6的时候记录数剧增为 55986
-- 7 的时候 55986
-- 8 的时候 19173960