Oracle--（Hierarchical Queries）层级查询
内容来自： Oracle® Database SQL Language Reference 11g Release 2 (11.2) E41084-03。
empolyees表来自hr方案，warehouses来自oe方案。
如果表中包含层级数据，可以使用层级查询子句按层级顺序选择数据行，形成层级树，形式如下：
下面是层级查询字句的火车图：
START WITH：指定层级的根行。即根据条件选取一些行，以这些行做最顶级行，获取子行。
CONNECT BY：指定层级中父行与子行关系。
一个层级查询的例子：
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
...
Oracle按如下方式处理层级查询：
- 如果存在连接，首先计算连接，无论连接在FROM子句中还是在WHERE子句中
- 计算CONNECT BY 条件
- 计算任何余下的WHERE子句
接下来，Oracle将使用上面计算获取的信息形成层级：
- Oracle选择满足START WITH 条件的根记录
- Oracle选择每条根记录的子行，每条子行满足CONNECT BY条件
- Oracle选择上一步子行的逐代子行。即Oracle首先选择上一步子行的子行，然后在选取这些子行的子行，依次向下。Oracle总是计算当前父行的CONNECT BY条件来选择子行。什么意思呢？一个包含层级数据的表中，包含了两个具有层级关系的列，如：EMPLOYEE_ID和MANAGER_ID，EMPLOYEE_ID是当前行的标识，而MANAGER_ID是父行的标识（即父行的EMPLOYEE_ID)。如何指定这个关系呢？Oracle使用了Prior操作符，PRIOR是一元操作符，作用于紧跟在它后面的字段名，如PRIOR EMPLOYEE_ID = MANAGER_ID，把该条件用于当前父行，PRIOR操作符导致Oracle取父行的EMPLOYEE_ID列的值，然后取子行的MANAGER_ID列的值与之比较，从而选出当前父行的子行。
- 如果查询包含一个无连接的WHERE子句，Oracle从层级中除去不满足WHERE子句的行。Oracle分别为每一行计算该条件，而不是当某一行不符合条件时，就移除它的所有子行。
- Oracle以层序的方式返回行。如图，返回记录的次序为1,2,3,4,5,6,7,8,9,10,11,12。看来，Oracle对每一次获取的子行进行了某种处理，而不是简单的追加。
下面是一些层级查询的例子：
1、CONNECT BY
这个例子仅使用CONNECT BY 子句定义雇员和经理的关系
SELECT employee_id, last_name, manager_id FROM employees CONNECT BY PRIOR employee_id = manager_id;
查询结果（部分）：
2、LEVEL
这个例子在查询结果中使用了LEVEL伪劣显示行在树关系中的层级
SELECT employee_id, last_name, manager_id, LEVEL FROM employees CONNECT BY PRIOR employee_id = manager_id;
查询结果（部分）：
3、START WITH
这个例子添加了START WITH子句指定起始根行，使用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;
查询结果（部分）：
4、LOOP
在上一个例子中，King没有上级领导，在他的员工中，有一个叫John Russell的部门（代码80）领导，现在更新King的领导为John Russell
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
报告错误：
ORA-01436: 用户数据中的 CONNECT BY 循环
错误的原因是King即是John Russell的领导又是他的员工，这就产生了一个循环，遇到这种情况Oracle会报告错误。但是，如果在CONNECT BY子句中使用NOCYCLE参数让Oracle返回结果。使用CONNECT_BY_ISCYCLE伪列可以显示哪些行包含循环。
执行语句：
SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE department_id = 80 START WITH last_name = 'King' CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 3 ORDER BY "Employee", "Cycle", LEVEL, "Path";
查询结果：
可见，Oracle去掉了无限循环的部分。
5、CONNECT_BY_ISLEAF
这是一个伪列，显示行在层级树中是否为叶子节点，1代表叶子节点，非1代表非叶子节点。
执行语句：
SELECT last_name "Employee", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE department_id=80 and CONNECT_BY_ISLEAF=1 START WITH last_name = 'King' CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 3;
查询结果：
这些3级节点全部为叶子节点，King出现在查询结果中，是因为我们在START WITH中指定了起始根行。
Oracle文档中给出了一个有趣的例子，展示如何使用层级查询把表中的一列数据转换为用逗号分隔的列表。执行语句：
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;
查询结果：
CONNECT BY r = PRIOR r + 1 子句指定了行的父子关系，如在当前父行中，PRIOR运算符取出r值，然后加1，查询满足r的值等于r+1的行，即从第一行开始，紧邻的两行均形成父子关系，前一行为父行，后一行为子行，由于r的特殊性（单向有序列表1,2,3,4…），实际上形成的树就是一个列表。运用SYS_CONNECT_BY_PATH显示每个节点的路径，使用CONNECT_BY_ISLEAF = 1条件仅选择叶子节点。
在这个例子中，每行的warehouse_id 恰好等于ROWNUM，可以换成其他列，形成不同的列表。
6、CONNECT_BY_ROOT 操作符
该操作符显示每一级父行的指定列值。
这个例子显示部门110的每个雇员的姓、每个雇员所有上级经理、雇员与经理的层级数、雇员与经理之间的路径。
执行语句：
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";
查询结果：
