oracle rownum和connect by 扫盲

最近需要实现一个功能：有这样一个数据

count   user_name

2             周

3             吴

实现结果：

user_name

周

周

吴

吴

吴

在网上找到了这样的方法：

with t as(
select 2 as count, '周' user_name from dual
union all
select 3,'吴' from dual
)
select * from t,
(select rownum rn from t connect by rownum <= 3) b
where count>=rn

非常有趣，就去了解了rownum和connect by

ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 (强调：先要有结果集)。简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1，而有其他大于1的值。

ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类 推。如果你用>,>=,=,between...and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除， 接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。

rowid 与 rownum 虽都被称为伪列，但它们的存在方式是不一样的，rowid 可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中唯一。只要记录没被搬动过，rowid是不变的。rowid 相对于表来说又像表中的一般列，所以以 rowid 为条件就不会有 rownum那些情况发生。
另外还要注意：rownum不能以任何基表的名称作为前缀。

 with  x  as  

    ( select  'aa'  chr,1 pid,4 id  from  dual

    union  all  

    select  'bb'  chr ,2,5 from  dual
/*        union  all  

    select  'cc'  chr ,3,6 from  dual*/)

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

 

第一行和第八行同属第一层级，第二到第四是第二层级，第三是第三层，看下面的图

如果初始行数是n,总层级是m，这棵树总行数函数是：

f(n,1)=n,

f(n,m)=n*f(n,m-1)+n;

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

接下来是细节：

       SELECT [LEVEL] ,column,expression,...

       FROM table_name

       [WHERE where_clause]

       [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

 [[START WITH start_condition] 讲的是这棵树从哪里开始；

prior_condition 具体这样：

prior 列名1=列名2

意思是之前一行的列名1=这一行的列名2

也可以这么写  列名1=prior列名2 ；

还有sys_connect_by_path的用法：

sys_connect_by_path( 列名，‘分隔符号’ )

结合例子就明白了：

 with  x  as  

    ( select  'aa'  chr,1 pid,4 id  from  dual

    union  all  

    select  'bb'  chr ,2,1 from  dual)
/*        select  level ,sys_connect_by_path(pid, '/' )  from  x

    start  with  pid= 1  

    connect  by  prior  pid =  id ;*/
     select  level ,ltrim(sys_connect_by_path( id , '/' ),'/')  from x

    start  with  pid= 1  

    connect  by  prior  pid =  id ;

好像看官应该不太容易明白。。。

自己练习吧。

又碰到这样一种情况

with t as(
select 2 as count, '周' user_name from dual
union all
select 3,'吴' from dual
/*union all
select 4,'郑' from dual*\*/
)
select rownum rn,t.user_name,level from t connect by rownum<=10

结果：

我就去找connect by rownum 和level的区别

重点是rownum，connect by rownum，没有其他条件，是直接递归迭代第一行（反复），然后判断rownum条件，不满足条件结束。正是因为
--递归如果没有start with则每行都是根，先第一轮递归，递归到rownum<n不满足后结束，然后其他根肯定不满足，只选出自身一行。
--则剩余其他几行都显示一次，因为第1行递归结束后，继续其他行，发现都不满足，结束。这个rownum不是select里的rownum
--connect by 1=1就不结束了

--没有start with,没有prior...的迭代，不管是level,还是rownum,每行都是根节点，自身或其它节点是子节点
--区别是connect by level会先深度搜索，也就是根---自身---其他节点
--connect by rownum则是不停迭代自身(第一行），然后判断rownum。。。

真相大白。

又折腾出一种情况：

with t as(
select 2 as count, '周' user_name from dual
union all
select 3,'吴' from dual
union all
select 4,'郑' from dual
)
select rownum rn,t.user_name,level from t 
connect by rownum=3

当rownum=固定值的情况，可以测试不同值，负数、0,1,2,等，结果不同；保底3个，看能否符合rownum情况决定是否会多一行和该位置；

还有加where条件，connect by加多个条件==。

http://www.cnblogs.com/szlbm/p/5806070.html

http://blog.csdn.net/haiross/article/details/17586565

http://blog.csdn.net/leshami/article/details/5616877

http://www.itpub.net/thread-1615513-1-1.html

http://www.itpub.net/forum.php?mod=viewthread&tid=1570306