connect by超乎你想象

你的名字 2023-07-20 10:37 5阅读 0赞

SQL中的connect by主要用在层级关系的查询，乍看确实可能有些绕，但在某些场景下，确实方便，语法格式如下，

{ CONNECT BY [ NOCYCLE ] condition [AND condition]...
    [ START WITH condition ] | START WITH condition 
    CONNECT BY [ NOCYCLE ] condition [AND condition]...}

关键字的解释，

> start with：指定起始节点的条件。  
> connect by：指定条件关系。  
> prior：查询上级行的限定符，格式：prior column1 = column2 or column1 = prior column2 and …。  
> nocycle：若数据表中存在循环行，那么不添加此关键字会报错，添加关键字后，便不会报错，但循环的两行只会显示其中的第一条循环行：该行只有一个子行，而且子行又是该行的祖先行。  
> connect\_by\_iscycle：前置条件:在使用了nocycle之后才能使用此关键字，用于表示是否是循环行，0表示否，1表示是。  
> connect\_by\_isleaf：是否是叶子节点，0表示否，1表示是。  
> level：level伪列，表示层级，值越小层级越高，level=1为层级最高节点。

创建测试表，id列表示序号，lead\_id表示上级id，name是名称，salary是当前name的薪水，

SQL> create table test(          
      2  id number,
      3  lead_id number,           
      4  name varchar2(30), 
      5  salary number);
      
      SQL> select * from test;
            ID    LEAD_ID NAME                               SALARY
    ---------- ---------- ------------------------------ ----------
             1          0 a                                  100000
             2          1 b1                                  50000
             3          1 b2                                  50000
             4          2 c1                                  20000
             5          2 c2                                  20000
             6          3 c3                                  20000
             7          3 c4                                  20000

将上面的数据，转换成这个结构，看起来更加清晰，a是大Boss，因此lead\_id是0，月薪10万，b1和b2是a管辖的两个部门Boss，他们的lead\_id是a的id=1，b1和b2的月薪是5万，c1和c2是b1管辖部门的员工，因此他们的lead\_id是b1的id=2，c3和c4是b2管辖部门的员工，因此他们的lead\_id是b2的id=3，c1-c4的月薪是2万，

![format_png][]

示例1：

查询以lead\_id为0开始的节点的所有直属节点，即查询从a开始所有管辖的员工信息，如下所示，priorname是该节点的上级，

SQL> select id, lead_id, name, prior name, salary from test
      2  start with lead_id = 0
      3  connect by prior id = lead_id;                              
            ID    LEAD_ID NAME                           PRIORNAME                          SALARY
    ---------- ---------- ------------------------------ ------------------------------ ----------
             1          0 a                                                                 100000
             2          1 b1                             a                                   50000
             4          2 c1                             b1                                  20000
             5          2 c2                             b1                                  20000
             3          1 b2                             a                                   50000
             6          3 c3                             b2                                  20000
             7          3 c4                             b2                                  20000

等价于以id为1开始的节点的所有直属节点，

SQL> select id, lead_id, name, prior name, salary from test
      2  start with id = 1 
      3  connect by prior id = lead_id;
            ID    LEAD_ID NAME                           PRIORNAME                          SALARY
    ---------- ---------- ------------------------------ ------------------------------ ----------
             1          0 a                                                                 100000
             2          1 b1                             a                                   50000
             4          2 c1                             b1                                  20000
             5          2 c2                             b1                                  20000
             3          1 b2                             a                                   50000
             6          3 c3                             b2                                  20000
             7          3 c4                             b2                                  20000

示例2：

查询以id为6开始的节点的所有直属节点，从图中可知，id=6是c3，他只有上级，没有下级，因此只是c3这条，

SQL> select id, lead_id, name, prior name, salary from test
      2  start with id = 6
      3  connect by prior id = lead_id;
            ID    LEAD_ID NAME                           PRIORNAME                          SALARY
    ---------- ---------- ------------------------------ ------------------------------ ----------
             6          3 c3                                                                 20000

示例3：

如下语句，还是以id为6开始，但是prior是lead\_id=id，不是上述prior id=lead\_id，看下返回信息，则是c3以及他的所有直属上级，

SQL> select id, lead_id, name, prior name, salary from test                 
      2  start with id = 6
      3  connect by prior lead_id = id;
            ID    LEAD_ID NAME                           PRIORNAME                          SALARY
    ---------- ---------- ------------------------------ ------------------------------ ----------
             6          3 c3                                                                 20000
             3          1 b2                             c3                                  50000
             1          0 a                              b2                                 100000

官方文档提到了，prior关键字可以位于操作符的任意一侧，

> PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.)

但是，从prior lead\_id=id和prior id=lead\_id的区别，你能想到什么？

没错，是树的遍历方向，这就是所谓神奇的地方。

(1) 如示例2，prior放在子节点端，则表示扫描树是以start with指定的节点作为根节点从上往下扫描。可能对应一个或多个分支。start with可以省略，如果省略，表示对所有节点都当成根节点分别进行遍历。  
2）如示例3，prior放在上级节点端，则表示扫描树是以start with指定的节点作为最低层子节点，从下往上扫描。顺序是子节点往上级节点扫描，直到根节点为止，这种情况只能得到一个分支。start with可以省略，如果省略，表示对所有节点都当成最低层子节点分别往根节点方向遍历。

为了看得更直观，通过lpad函数、level伪列，格式化层级，可以看到a是第一层级，b1和b2是第二层级，其他是第三层级，

select id, lead_id, lpad(' ', level*2, ' ')||name, prior name, salary, connect_by_isleaf from test
    start with lead_id = 0
    connect by prior id = lead_id;

![format_png 1][]

以上介绍的，其实只是connect by一些最基本的使用，connect by的用法上，远不止这些，通过几行SQL，能实现更加复杂的场景。

说句题外话，不积跬步无以至千里，争取我们都能做到月入10万的大Boss，就从关注我的公众号开始。

参考文献：

https://www.cnblogs.com/wanggang-java/p/10916426.html\#\_label2

https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/Hierarchical-Query-Operators.html\#GUID-95F6A554-C6FE-42CD-88A6-7A1C162ED964

**近期热文：**

《[公众号600篇文章分类和索引][600]》

《[Oracle ACE，一段不可思议的旅程][Oracle ACE]》

《[Oracle 19c之RPM安装][Oracle 19c_RPM]》

《[应用执行慢的问题排查路径][Link 1]》

《[ACOUG年会感想][ACOUG]》

《[千万级表数据更新的需求][Link 2]》

《[探寻大表删除字段慢的原因][Link 3]》

《[一次Oracle bug的故障排查过程思考][Oracle bug]》

《[新增字段的一点一滴技巧][Link 4]》

《[对recursive calls的深刻理解][recursive calls]》

《[《Oracle Concept》第三章 - 12][Oracle Concept_ - 12]》

《[一次惊心动魄的问题排查][Link 5]》

《[Java日期中“y”和“Y”的区别][Java_y_Y]》

《[英超梦幻之行][Link 6]》

《[藤子不二雄博物馆之行][Link 7]》

《[传控Tiki-Taka战术解惑][Tiki-Taka]》

[format_png]: /images/20230528/15e095a473314289bca4e99b924f651d.png
[format_png 1]: /images/20230528/b7b71e971c844903808032beb298abd6.png
[600]: https://blog.csdn.net/bisal/article/details/104548761
[Oracle ACE]: https://blog.csdn.net/bisal/article/details/94386471
[Oracle 19c_RPM]: https://blog.csdn.net/bisal/article/details/100909708
[Link 1]: https://blog.csdn.net/bisal/article/details/102480420
[ACOUG]: https://blog.csdn.net/bisal/article/details/103675746
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814beaddf6c2fcd242591e4892a3ac21a272f3307b9771a8ca9b76ef96b1a7bd8249c564fa&idx=1&mid=2247487001&scene=21&sn=15394f15aedee7f8f261fa7294df08a5#wechat_redirect
[Link 3]: https://blog.csdn.net/bisal/article/details/86635285
[Oracle bug]: https://blog.csdn.net/bisal/article/details/101729688
[Link 4]: https://blog.csdn.net/bisal/article/details/103626212
[recursive calls]: https://blog.csdn.net/bisal/article/details/100074863
[Oracle Concept_ - 12]: https://blog.csdn.net/bisal/article/details/100788405
[Link 5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea8149e8ddf6c0fea92181efe658b69c6acd52b691cd562e830f0e63a565215a136105272d27&idx=1&mid=2247486491&scene=21&sn=584c4ac132b69a1e319b2a2bedfd49e2#wechat_redirect
[Java_y_Y]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815467ddf6dd717251b9f1b6ccd1ec838908ff11d6981159db79c56a2fa554ea5f483c969f&idx=1&mid=2247487892&scene=21&sn=7aa998ef83b3ba3308615808dc3ceba2#wechat_redirect
[Link 6]: https://blog.csdn.net/bisal/article/details/103044518
[Link 7]: https://blog.csdn.net/bisal/article/details/103379370
[Tiki-Taka]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814e5cddf6c74a763ac9fac98f94e538bca009fdcff3795533026269686983ceae190fe883&idx=1&mid=2247486383&scene=21&sn=d4c54d7033fc9a21d622e5937cddf64e#wechat_redirect