Oracle connect by level

 

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;

-- 准备一张一列三行的测试表

 

2. 简单测试和解释

select id,level from t connect by level<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的子树，然后先根遍历 

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

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

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

-- 表t有3条记录，N=3

-- 条件 level<4，L=3

-- 那么会生成3棵高度为3的子树，然后先根遍历

 

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结果不在截图展示，请自行测试

 

6. 技巧继续衍生

select * from t,(select level from dual connect by level<=5);

-- 使用笛卡尔积，将原表复制出N份记录

 

insert into t select * from t;

commit;

-- 相似的制造数据的方法

-- 重复几次你的表就会爆炸掉

-- BOOOOOM！

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