connect by超乎你想象

bisal的个人杂货铺 2020-03-31

719

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;