Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Conntect by 问题

SQL：1、connect by 最常见的写法

select SYS_CONNECT_BY_PATH(sal, ',')
  from (select sal,
               ROW_NUMBER() over(PARTITION BY deptno ORDER BY sal) rn,
               ROW_NUMBER() over(PARTITION BY deptno ORDER BY sal) - 1 as dn
          from scott.emp
         where deptno = '20')
 start with rn = 1
connect by prior rn = dn

显示结果：

,1200
,1200,1650
,1200,1650,4462.5
,1200,1650,4462.5,4500
,1200,1650,4462.5,4500,4500 没问题

写法二：

select SYS_CONNECT_BY_PATH(sal, ',')
        from (select sal,
               ROW_NUMBER() over(ORDER BY sal) rn,
               ROW_NUMBER() over(ORDER BY sal) - 1 as dn
          from scott.emp where deptno = '20')
          start with rn = 1
        connect by prior rn = dn

显示结果同上 没问题；

写法三：

with ss as
        (select sal,
               ROW_NUMBER() over(PARTITION BY deptno ORDER BY sal) rn,
               ROW_NUMBER() over(PARTITION BY deptno ORDER BY sal) - 1 as dn
          from scott.emp where deptno = '20')
   select SYS_CONNECT_BY_PATH(sal, ',') from ss  start with rn = 1
        connect by prior rn = dn

结果：同上

写法四：

with ss as
        (select sal,
               ROW_NUMBER() over(ORDER BY sal) rn,
               ROW_NUMBER() over(ORDER BY sal) - 1 as dn
          from scott.emp where deptno = '20')
   select SYS_CONNECT_BY_PATH(sal, ',') from ss  start with rn = 1
        connect by prior rn = dn

结果：

,1200
,1200,1650

结果看出，只递归了2次。