Hierarchical Queries—组织部门关系整理

前言：
写了一堆的java代码，现在发现，解决这个问题的最好的手段是用sql中的Hierarchical Queries（层级查询）
参考Hierarchical Queries: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm

-3 start with 的条件从其他表动态获取的方法

用先连接后start with 的办法
http://www.itpub.net/thread-1710588-1-1.html
类似这种

--  先连接 再追溯
select addr.*,a.id ,a.DEPT_ID
from BI_TRADE_INFO addr,SYS_P_DEPARTMENT_3   a
where 1=1
start with addr.dept_id=a.id
connect by a.id=prior a.parent_id

-2 只提取一条链 用start with

-1 原理

利用递归查询的方法实现的，所以最后查询的结果数量会增多，如下面这个例子，表中只有3条数据，查询结果编程了6条。

0 关于prior

通俗的来讲就是 ，超前看 还是朝后看

1 语法

下面这张表

LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL
------------------------- ----------- ---------- ----------
King                              100                     1
Cambrault                         148        100          2
Bates                             172        148          3
Bloom                             169        148          3
Fox                               170        148          3
Kumar                             173        148          3
Ozer                              168        148          3
Smith                             171        148          3
De Haan                           102        100          2
Hunold                            103        102          3
Austin                            105        103          4
Ernst                             104        103          4
Lorentz                           107        103          4
Pataballa                         106        103          4
Errazuriz                         147        100          2
Ande                              166        147          3
Banda                             167        147          3

SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",  //这是一个循环伪列
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3     //层级
   START WITH last_name = 'King'   //以king 为祖宗节点 如果没有 可以有多个根节点
   CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;   //NOCYCLE 表示就算有循环
   也要显示出来

Employee                   Cycle  LEVEL Path
------------------------- ------ ------ -------------------------
Russell                        1      2 /King/Russell
Tucker                         0      3 /King/Russell/Tucker
Bernstein                      0      3 /King/Russell/Bernstein
Hall                           0      3 /King/Russell/Hall
Olsen                          0      3 /King/Russell/Olsen
Cambrault                      0      3 /King/Russell/Cambrault
Tuvault                        0      3 /King/Russell/Tuvault
Partners                       0      2 /King/Partners
King                           0      3 /King/Partners/King
Sully                          0      3 /King/Partners/Sully
McEwen                         0      3 /King/Partners/McEwen
...
CONNECT_BY_ROOT Examples 

2 实例

2.1 实例一：组织部门关系 ：

1.导入到数据库中

2.用下列的sql语句进行查询

我的理解
1层级关系，本意是想生成一个树，根是不能有多个的，你可以自己设置根，也就是开始的点，如果不是顶级节点，就可能是树的一部分，里面可能有数据就没没用了。
2如果层次关系不是一棵树就有可能出现循环的问题，然后就需要用 NOCYCLE 这个关键词 ，另外为了显示，循环的结果集，可以用 CONNECT_BY_ISCYCLE “Cycle” ，他会记录循环的记录
3下面就是一个有循环的组织关系

 select ia.id,name,IA.PARENT_ID,level, CONNECT_BY_ISCYCLE "Cycle" from  SYS_P_DEPARTMENT ia CONNECT
  BY  NOCYCLE prior ia.id=ia.parent_id ORDER BY "Cycle" ;

3 结果

2.2 实例二 投稿排行根据顶级部门统计数量

出自：https://blog.csdn.net/crzzyracing/article/details/75216431
    找出编号为101的雇员及其所有上司  
    
        select level, t.*
          from myemp t
         start with employee_id = 101
        connect by employee_id = prior manager_id;

        别人的工号 = 我的经理编号 ---> 别人是我的经理 & 别人是我经理的经理 ---> 我的所有上司

工会查找顶级部门

select ia.id,name,IA.PARENT_ID,level, CONNECT_BY_ISCYCLE "Cycle" from  SYS_P_DEPARTMENT ia 
START WITH id = 533
CONNECT BY  NOCYCLE   ia.id=prior ia.parent_id ORDER SIBLINGS BY id ;

2.3实例三 组织部门三级结构

SELECT c.id id1,c.name name1,t.id1 id2,t.name1 name2,t.id2 id3,t.name2 name3 FROM
SYS_P_DEPARTMENT c
LEFT JOIN
(
SELECT
	A.id id1,A."NAME" name1,a.PARENT_ID id2,b."NAME" name2
FROM
	"SYS_P_DEPARTMENT" A
LEFT JOIN "SYS_P_DEPARTMENT" B ON A .PARENT_ID = B.id
)t
on c.parent_id=t.id1

2.4 查找爸爸 和 爷爷的例子

这个需求是因为相同工会的不同部门之间需要可以相互审核，所以加上这个，只要父辈有一个匹配就认为是同一个部门的。

with tmp as(
      select a.*, level leaf        
      from SYS_P_DEPARTMENT_3 a  
START with   a.id='373'
-- 			where a.parent_id is not NULL
      connect by prior a.parent_id = a.id )
select *                               
from tmp                             
where leaf = 2 or  leaf = 3 or  leaf = 1;  //跟自己相同或者有共同的父辈就是一个工会的