当表中含有分层次的数据，可以使用层次查询获取分层的数据。
以上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