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

最新推荐文章于 2021-04-11 06:34:58 发布

原创最新推荐文章于 2021-04-11 06:34:58 发布 · 1.9k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

ORACLE DBA 专栏收录该内容

36 篇文章

订阅专栏

本文详细介绍了SQL中connectby函数的四种常见写法，并通过具体例子展示了每种写法的显示结果，帮助读者理解并掌握connectby函数的应用。

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次。