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

---------------------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#返回的结果不同！

--------------------------------------------------------------------------------------------华丽的分割线

• 博文量
35
• 访问量
95763