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
近期热文:
《英超梦幻之行》