ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于connect by level与connect by rownum 的区别

关于connect by level与connect by rownum 的区别

原创 Linux操作系统 作者:0x0x0x 时间:2012-06-26 13:56:02 0 删除 编辑
这是我在itpub发的第一个帖子:
http://www.itpub.net/thread-1615513-1-1.html
 
---------------------one column in WITH,the results are confused!!
---------one connect
--1# use level connect,return rownum,level
select rownum,level from dual connect by level<=3
--2# use rownum connect,return rownum,level
select rownum,level from dual connect by rownum<=3
--1#和2#返回的结果一样:这点好理解!
--------tow connect
--3# rerurn 1~12
with t as (select level from dual connect by level<=3)
select rownum from t connect by level<=2
--4# return 1~4
with t as (select level from dual connect by level<=3)
select rownum from t connect by rownum<=2
--5# rerurn 1~12
with t as (select level from dual connect by rownum<=3)
select rownum from t connect by level<=2
--6# return 1~4
with t as (select level from dual connect by rownum<=3)
select rownum from t connect by rownum<=2
--3#和5#相同结果,4#和6#结果相同,但是为什么3#和4#返回的结果不同呢?
求解释!

---------------------two columns in WITH,the results are amazing!!
-----------------one connect
--7# return 1,2,2,2
with t as (select 1 as A from dual union all select 2 as A from dual connect by level<=3)
select A from t
--8# return 1,2,2,2
with t as (select 1 as A from dual union all select 2 as A from dual connect by rownum<=3)
select A from t
--7#和8#结果相同

------------------tow connect
--9# return 20 colums
with t as (select 1 as A from dual union all select 2 as A from dual connect by level<=3)
select A from t connect by level<=2
--10# return 5 columns
with t as (select 1 as A from dual union all select 2 as A from dual connect by level<=3)
select A from t connect by rownum<=2
--11# return 20 colums
with t as (select 1 as A from dual union all select 2 as A from dual connect by rownum<=3)
select A from t connect by level<=2
--12# return 5 columns
with t as (select 1 as A from dual union all select 2 as A from dual connect by rownum<=3)
select A from t connect by rownum<=2
--9#和11#返回的结果相同,10#和12#返回结果相同,但是9#和10#返回的结果不同!
求解释!
请问connect by level 和connect by rownum 的不同点在哪?

以下是给位大侠给予的答复:
--------------------------------------------------------------------------------------------华丽的分割线

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26977915/viewspace-733810/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-06-26

  • 博文量
    35
  • 访问量
    95763