oracle的connect by 的casea介绍

connect by 的casea介绍


首先介绍一下connect by作用：对于数据有着严密的层级关系的表，我们有时候希望能够把有着
父子关系或者叫上下级关系的数据一次性展现出来，这个时候传统的sql 语法并不能就解决问题，
例如一个部门有一个总经理，多个副经理，每个下面又有多个总监，总监下面是员工，
我们设计表的时候，肯定只有一个字段来记录员工的上级，并不会记录他的上上级，那么我们
想把某个副经理的下面的所有员工都列出来的时候，就存在递归查找底层员工的情况，这种
就需要用到递归遍历，不同的DB给出了不同的解决办法，如DB2可以使用with+内嵌递归逻辑的
sql实现，oracle 提供了connect by的语法来实现。下面简单的看个例子：

note:
nocycle关键字用来规避死循环，这个参数的意思就是不要进入死循环
example1:

drop table emp;
create table emp( emp_no number , manager_no number, name varchar2(100));
insert into emp values(1,,'总经理');
insert into emp values(2,1,'副经理1');
insert into emp values(3,1,'副经理2');
insert into emp values(4,2,'总监1');
insert into emp values(5,3,'总监2');
insert into emp values(6,4,'员工6');
insert into emp values(7,4,'员工7');
insert into emp values(8,5,'员工8');
insert into emp values(9,5,'员工9'); 
commit;
--得到结果如下： 
ZXC@trade>select * from emp;

    EMP_NO MANAGER_NO NAME
---------- ---------- --------------------
         2          1 副经理1
         3          1 副经理2
         4          2 总监1
         5          3 总监2
         6          4 员工6
         7          4 员工7
         8          5 员工8
         9          5 员工9

1个总经理两个副经理，每个副经理都有一个总监，每个总监都有两个员工。
下面列出副总1的所有员工情况:

set linesize 300 pagesize 100
col path for a30
col name for a20
select EMP_NO,MANAGER_NO,NAME from emp   start with emp_no=2
connect by prior emp_no=manager_no;
    EMP_NO MANAGER_NO NAME
---------- ---------- -----------------------------
         2          1 副经理1
         4          2 总监1
         6          4 员工6
         7          4 员工7
--
这里用2为根节点进行查找：（父节点即为管理者）
#####注意：这里MANAGER_NO是EMP_NO的上级领导，所以connect by 是 领导号=员工编号：MANAGER_NO(领导) = prior EMP_NO(下属员工号)，
即通过这个确定pror的where条件---领导对应的员工编号这样一级一级的关系。

SELECT EMP_NO,MANAGER_NO,NAME,connect_by_iscycle, SYS_CONNECT_BY_PATH(MANAGER_NO, '/') "Path"
from emp start with MANAGER_NO=2 
connect by nocycle MANAGER_NO = prior EMP_NO ;
    EMP_NO MANAGER_NO NAME       CONNECT_BY_ISCYCLE Path
---------- ---------- ---------- ------------------ --------------
         4          2 总监1                       0 /2
         6          4 员工6                       0 /2/4
         7          4 员工7                       0 /2/4
--
从这里就可以看出从MANAGER_NO为2（领导号）开始的所属关系
从这里就知道1是2，2是3，3是5的父节点，5是6的父节点。


这里用1为根节点进行查找：
SELECT EMP_NO,MANAGER_NO,NAME,connect_by_iscycle, SYS_CONNECT_BY_PATH(MANAGER_NO, '/') "Path"
from emp start with MANAGER_NO=1 
connect by nocycle MANAGER_NO = prior EMP_NO ;

    EMP_NO MANAGER_NO NAME                 CONNECT_BY_ISCYCLE Path
---------- ---------- -------------------- ------------------ ------------------------------
         2          1 副经理1                              0 /1
         4          2 总监1                                 0 /1/2
         6          4 员工6                                 0 /1/2/4
         7          4 员工7                                 0 /1/2/4
         3          1 副经理2                              0 /1
         5          3 总监2                                 0 /1/3
         8          5 员工8                                 0 /1/3/5
         9          5 员工9                                 0 /1/3/5
---
从这里就可以看出从MANAGER_NO（领导号）为1开始的所属关系：
从这里就知道1是2和3的父节点
2是4的父节点，4是6和7的父节点
3是5的父节点，5是8和9的父节点。



-------------------------------------------------------------------example2:
drop table t1;
create table t1 (id int, fst_child int, snd_child int);
 
--base data
insert into t1 values (1, 2, NULL);
insert into t1 values (2, 3, 4);
insert into t1 values (3, 5, NULL);
insert into t1 values (4, 5, NULL);
insert into t1 values (5, 6, NULL);
insert into t1 values (6, 2, NULL);


ZXC@trade>select * from t1;

        ID  FST_CHILD  SND_CHILD
---------- ---------- ----------
         1          2
         2          3          4
         3          5
         4          5
         5          6
         6          2
-
#####注意：这里ID是FST_CHILD的上级领导，所以connect by ID（上级领导号）= prior fst_child(子员工号)，即通过这个确定pror的where条件
这里就是从领导号为1即id = 1查找关系
SELECT id, fst_child, nvl(snd_child,99999) snd_child, connect_by_iscycle, SYS_CONNECT_BY_PATH(id, '/') "Path" 
from t1 start with id = 1 
connect by nocycle id = prior fst_child;

        ID  FST_CHILD  SND_CHILD CONNECT_BY_ISCYCLE Path
---------- ---------- ---------- ------------------ ------------------------------
         1          2      99999                  0 /1
         2          3          4                  0 /1/2
         3          5      99999                  0 /1/2/3
         5          6      99999                  1 /1/2/3/5
--
从这里就知道1是2，2是3，3是5的父节点，5是6的父节点。

SELECT id, fst_child, nvl(snd_child,99999) snd_child, connect_by_iscycle, SYS_CONNECT_BY_PATH(id, '/') "Path"
from t1 start with id = 1
connect by nocycle ((id = prior fst_child) or (id = prior snd_child));
        ID  FST_CHILD  SND_CHILD CONNECT_BY_ISCYCLE Path
---------- ---------- ---------- ------------------ ------------------------------
         1          2      99999                  0 /1
         2          3          4                  0 /1/2
         3          5      99999                  0 /1/2/3
         5          6      99999                  1 /1/2/3/5
         4          5      99999                  0 /1/2/4
         5          6      99999                  1 /1/2/4/5
--

reference：
https://blogs.oracle.com/database4cn/post/%E4%B8%80%E4%B8%AAconnect-by-%E5%BA%94%E7%94%A8%E7%9A%84case