dual的一些巧妙用法

最新推荐文章于 2024-01-24 13:42:56 发布

吃鱼的羊

最新推荐文章于 2024-01-24 13:42:56 发布

阅读量2.8k

点赞数

分类专栏： ORACLE

ORACLE 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

dual是一个只有一条记录的表

select * from dual

DUMMY
X

1，生成序列

select LEVEL from dual connect by LEVEL <= 3; / select rownum from dual connect by rownum <= 3;

select LEVEL from dual connect by 1=1;

2, 配合to_date函数生成一个从2015-1-1开始的日期序列

SELECT LEVEL,(TO_DATE('20150101', 'yyyy-mm-dd') + LEVEL - 1) CUR_DATE FROM DUAL
CONNECT BY LEVEL <= SYSDATE - TO_DATE('20150101', 'yyyy-mm-dd') + 1

3， with x as

( select 'aa' chr from dual

union all

select 'bb' chr from dual)

select level ,chr,lpad( ' ' ,( level - 1 )* 5 , '-' )||chr other from x connect by level <= 3

            LEVEL CHR    OTHER
    1     aa     aa
    2     aa     ---- aa
    3     aa     --------- aa
    3     bb     --------- bb
    2     bb     ---- bb
    3     aa     --------- aa
    3     bb     --------- bb
    1     bb     bb
    2     aa     ---- aa
    3     aa     --------- aa
    3     bb     --------- bb
    2     bb     ---- bb
    3     aa     --------- aa
    3     bb     --------- bb
    可见是全部level的树形结构，当扫描对象是dual时，即一个level只生成一条记录.

aaa表有3行数据：a，b，c

LEVEL伪列表示树的深度（或叫高度）

下图为select id,level from aaa connect by level<4时递归查询到的树状结构：

由上图，可以得出规律如下：

N+N的二次方+。。。。。+N的LEVEL次方

其中，N表示表中有N条记录，LEVEL表示上述树状图中的树的层数，也就是指connect by 子句中的level伪列（或是rownum伪列）值。

树每增加一层，则N+N的二次方+。。。。。+N的LEVEL+1次方=N+N*（N+N的二次方+。。。。。+N的LEVEL次方）。

于是可以总结出
F(N,l)=∑power(N,p), p取值为[1,l)，即level=1时，power(3,1)=3 level=2时，power(3,2)=9，即12-3，level=3时，power(3,3)=27，即39-12。

从而得出如下结论：

假设表中有N条记录，则记F(N,l)为select id,level from t connect by level<l 的结果集数目。那么：
F(N,1)=N
F(N,l) = F(N,l-1)*N+N

注释：

当连接条件（connect by条件）没有限制记录之间的关系（即 connect by里没有类似 id=prior pid的条件，而是 ~~connect by rownum<xxx 或~~ connect by level<xxx ）时，每一条记录都会作为自己或者其他记录的子节点，也就说，每一条记录的子节点就是表上所有的记录。而树的层数就是~~rownum（或是~~level）值。

这就是Oracle采用了深度优先的算法。

仅当FROM后面的表只有一行（比如DUAL)时，LEVEL和ROWNUM可以互换。

with t as (select level l from dual connect by level<=3)
select rownum,l,LEVEL from t connect by level<=2

---- 总共两层数据，第二层是 t 的自身笛卡尔积，3+3*3 = 12 行
ROWNUM       L    LEVEL
---------- ---------- ----------
      1       1       1
      2       1       2
      3       2       2
      4       3       2
      5       2       1
      6       1       2
      7       2       2
      8       3       2
      9       3       1
      10       1       2
      11       2       2
      12       3       2

12 rows selected.

with t as (select level l from dual connect by level<=3)
select rownum,l,LEVEL from t connect by rownum=2

---- 总共两层数据，第二层受制于CONNECT BY条件，只有一行

ROWNUM       L    LEVEL
---------- ---------- ----------
      1       1       1
      2       1       2  ------ ROWNUM的生成顺序是深度优先，第二层第一条得到ROWNUM=2，在这里停止
      3       2       1  ------ 第一层数据不受CONNECT BY制约，全部输出。
      4       3       1

另一问题类似。

对于有N条记录的来说，如果没有递归条件，直接connect by level,先深度搜索，再广度，则每个节点作为根节点，然后自身和其他节点为子节点，然后下个子节点还包括自身和其他节点，然后同样迭代。所以，总共记录数有N*2^0+N*2^1+......... 其中0,1....为level
则记F(N,l)为 select id,level from t connect by level<l 的结果集数目
那么，F(N,1)=N

F(N,l) = F(N,l-1)*N+N

于是可以总结出
F(N,l)=∑power(N,p), p取值为[1,l)

总记录数N，level层数P
结果集数：T=∑N^x(x=1...p)
比如，总记录数为3，层数为3
则结果集数：3^1 +3^2 + 3^3 = 3+9+27=39

connect by rownum及connect by level