专栏首页bisal的个人杂货铺connect by超乎你想象

connect by超乎你想象

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万,

示例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;

以上介绍的,其实只是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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • select from update row的实现

    DTCC大会上,阿里江疑的演讲中提到一个:select from update hot row;

    bisal
  • 一次夜维SQL的性能优化

    最近单位搬家,从国家会议中心,搬往空气清新的顺义后沙峪,搬迁之前的完结上线中,碰见了一些棘手的问题,有一些值得借鉴的地方。

    bisal
  • 外键要建立索引的原理和实验

    项目中,我们要求凡是有主子关系的表都要使用外键约束,来保证主子表之间关系的正确,不推荐由应用自己控制这种关系。

    bisal
  • 一道很有意思的Redis面试题,我选出了一些优质评论

    起源于我在一个短视频中分享的一道面试题,当然,这道面试题我确实在工作中用过,只是业务场景不同。

    wujiuye
  • MySQL外键约束

    外键其实很好理解,简单的说就是两张表建立一个连接关系。这里我们那主表A和副表B举例,我A表中有用户信息,B表中有用户订单信息。要是数据完整对应起来,肯定是需要把...

    卡二条的技术圈子
  • MyBatis学习总结(五)——实现关联表查询

    一枝花算不算浪漫
  • TP踩过的坑[修改功能]前台要给input加上隐藏属性

    简单、
  • Mybatid关联表查询

    一、一对一关联  1.1、提出需求   根据班级id查询班级信息(带老师的信息) 1.2、创建表和数据   创建一张教师表和班级表,这里我们假设一个老师只负...

    汤高
  • MyBatis 实现关联表查询

    一、一对一关联  1.1、提出需求   根据班级id查询班级信息(带老师的信息) 1.2、创建表和数据   创建一张教师表和班级表,这里我们假设一个老师只负责教...

    庞小明
  • 【SSH快速进阶】——Hibernate 多对多映射

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/huyuyang6688/article/...

    DannyHoo

扫码关注云+社区

领取腾讯云代金券