Oracle SQL层次查询、Hierarchical Queries、connecty by

当表中含有分层次的数据，可以使用层次查询获取分层的数据。

以上1为根节点，level为1
3、5、6、8、11、12为叶子节点，level为3或4
2为3的父节点，1为2的父节点，或者说是紧挨着的上级节点

--安装数据库时如果选择的示例方案，在hr方案下面有一张employees表，这张表里含有分层的数据
--每个员工都有一个employee_id和manager_id,manager_id其实就是该员工所属上级的employee_id
--因此，通过一个员工的employee_id找到哪些记录的manager_id与其相等，即为自己的下层节点，以此类推，可以看出数据是分层的

SQL> select EMPLOYEE_ID,LAST_NAME,MANAGER_ID from employees; 

EMPLOYEE_ID LAST_NAME         MANAGER_ID
----------- ----------------- ----------
198         OConnell          124
199         Grant             124
200         Whalen            101
201         Hartstein         100
202         Fay               201
203         Mavris            101
204         Baer              101
205         Higgins           101
206         Gietz             205
100         King
101         Kochhar           100
......
--比如从上面可以看到员工ID为206的Gietz的上级的员工ID为205，也就是Higgins，Higgins的上级员工ID为101，而且还有其他几位员工的上级员工ID都是101.

语法：

在一个层次查询中connect by语句是不能缺少的
start with 表示从哪个节点开始进行层次的关联
prior id=p_id 表示父节点的id等于子节点的p_id，这个自己体会一下。

例子：

--1.该查询可以显示员工之间的层级关系，指定了上级节点的employee_id等于下级节点的manager_id

SELECT employee_id, last_name, manager_id
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID
----------- ------------------------- ----------
        101 Kochhar                          100
        108 Greenberg                        101
        109 Faviet                           108
        110 Chen                             108
        111 Sciarra                          108
        112 Urman                            108
        113 Popp                             108
        200 Whalen                           101
        203 Mavris                           101
        204 Baer                             101
. . .


--2.带上LEVEL伪列可以更加清楚看到层级关系

SELECT employee_id, last_name, manager_id, LEVEL
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID      LEVEL
----------- ------------------------- ---------- ----------
        101 Kochhar                          100          1
        108 Greenberg                        101          2
        109 Faviet                           108          3
        110 Chen                             108          3
        111 Sciarra                          108          3
        112 Urman                            108          3
        113 Popp                             108          3
        200 Whalen                           101          2
        203 Mavris                           101          2
        204 Baer                             101          2
        205 Higgins                          101          2
        206 Gietz                            205          3
        102 De Haan                          100          1
...


--3.START WITH 指定从那个父节点开始往下展开，order by SIBLINGS 意思是，一个父节点下面同一级有多个子节点，给他们进行排序

SELECT last_name, employee_id, manager_id, LEVEL
      FROM employees
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id = manager_id
      ORDER SIBLINGS BY last_name;

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
...


--4.因为King是公司老大，没有上级领导，现在我们故意将King的领导更新为他的一个下属，这时候会出现错误。因为king的领导是X，X的领导是king，死循环。

UPDATE employees SET manager_id = 145
   WHERE employee_id = 100;

SELECT last_name "Employee", 
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3 AND department_id = 80
   START WITH last_name = 'King'
   CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;

ERROR:
ORA-01436: CONNECT BY loop in user data


--5.对于上面的循环问题，我们指定 nocycle,这样即使有循环存在，也不会报错。可以看出限制了level<=3,限定了树只有3层，所以也不会出现死循环。
--还有就是connect_by_iscycle,可以找出包含循环的行。

SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3 AND department_id = 80
   START WITH last_name = 'King'
   CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4
   ORDER BY "Employee", "Cycle", LEVEL, "Path";

Employee                       Cycle      LEVEL Path
------------------------- ---------- ---------- -------------------------
Abel                               0          3 /King/Zlotkey/Abel
Ande                               0          3 /King/Errazuriz/Ande
Banda                              0          3 /King/Errazuriz/Banda
Bates                              0          3 /King/Cambrault/Bates
Bernstein                          0          3 /King/Russell/Bernstein
Bloom                              0          3 /King/Cambrault/Bloom
Cambrault                          0          2 /King/Cambrault
Cambrault                          0          3 /King/Russell/Cambrault
Doran                              0          3 /King/Partners/Doran
Errazuriz                          0          2 /King/Errazuriz
Fox                                0          3 /King/Cambrault/Fox
...


--6.展示了将列数据转换为逗号分隔的行数据，使用了connect_by_isleaf,sys_connect_by_path语法

SELECT LTRIM(SYS_CONNECT_BY_PATH (warehouse_id,','),',') FROM
   (SELECT ROWNUM r, warehouse_id FROM warehouses)
   WHERE CONNECT_BY_ISLEAF = 1
   START WITH r = 1
   CONNECT BY r = PRIOR r + 1
   ORDER BY warehouse_id; 
 
LTRIM(SYS_CONNECT_BY_PATH(WAREHOUSE_ID,','),',')
--------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9


--7.sys_connect_by_path 语法为每行产生一条从根节点到当前节点的路径

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE LEVEL > 1 and department_id = 110
   CONNECT BY PRIOR employee_id = manager_id
   ORDER BY "Employee", "Manager", "Pathlen", "Path";

Employee        Manager            Pathlen Path
--------------- --------------- ---------- ------------------------------
Gietz           Higgins                  1 /Higgins/Gietz
Gietz           King                     3 /King/Kochhar/Higgins/Gietz
Gietz           Kochhar                  2 /Kochhar/Higgins/Gietz
Higgins         King                     2 /King/Kochhar/Higgins
Higgins         Kochhar                  1 /Kochhar/Higgins


--8.connect_by_root 语法返回当前节点的根节点，下面的语句是求出部门ID为110的的员工和其下属员工的工资总额。比如小强工资为100，他的上级为150，
--结果就是小强的总额为100，因为他是底层员工，小强老板工资总额为250.就是找到一个节点，统计这个节点以及他下层节点的总额。

SELECT name, SUM(salary) "Total_Salary" FROM (
   SELECT CONNECT_BY_ROOT last_name as name, Salary
      FROM employees
      WHERE department_id = 110
      CONNECT BY PRIOR employee_id = manager_id)
      GROUP BY name
   ORDER BY name, "Total_Salary";

NAME                      Total_Salary
------------------------- ------------
Gietz                             8300
Higgins                          20300
King                             20300
Kochhar                          20300

值的注意的地方:

--在使用层次查询，connect by的时候，如果省略prior xx=yy子句，仅仅指定level的深度，
Oracle会用所有level=n的节点来作为所有level=n的子节点，产生level=n+1的节点(这里指的是在同一个根节点下面的层级)

--connect by 可以用来构造递增的数，level=1有一个1个元素，然后用这个元素来产生2，level=2只有一个元素，用来产生3

SQL> select dummy,level from dual connect by level<=3;

DUMMY      LEVEL
-----      ----------
X          1
X          2
X          3


--level=1有2个元素，然后这2个元素分别作为level=1两个元素的下层节点。这样level=2就有4个节点，
--但是level=3不会用level=2的所有4个元素来和level=2的4个节点关联，因为level=2的4个节点分属两个不同的根节点
--如下图
   a           b
 a   b      a    b
a b a b    a b  a b

SQL> select id,name,level from (
     select 1 id,'a' name from dual union all select 2,'b' from dual
     ) connect by level<=3;

        ID Name      LEVEL
---------- ----      ----------
         1 a          1
         1 a          2
         1 a          3
         2 b          3
         2 b          2
         1 a          3
         2 b          3
         2 b          1
         1 a          2
         1 a          3
         2 b          3
         2 b          2
         1 a          3
         2 b          3

14 rows selected.

这个小技巧可以在很多地方使用，比如拆分字符串，有兴趣看下面的关联文章。
http://blog.csdn.net/seandba/article/details/72629724
http://blog.csdn.net/seandba/article/details/72582665

以上内容大部分均来自Oracle11g官方文档
Oracle® Database SQL Language Reference
11g Release 2 (11.2)
E26088-03