Hierarchical Queries in Oracle

有一些表存在层级关系，例如Oracle的示例表hr.employees：

LAST_NAME	EMPLOYEE_ID	MANAGER_ID
King	100	-
Kochhar	101	100
De Haan	102	100
Hunold	103	102
Ernst	104	103

可以看到每个员工有自己的manager，从而形成类似这样的层级关系：

 King是最大的领导，因此他就是root，下面有其它小领导并形成了分支和层级。下面来看Oracle提供的Hierarchical Queries能做什么。

SELECT last_name, employee_id, manager_id
FROM hr.employees
CONNECT BY PRIOR employee_id = manager_id

这里connect by prior表明了层级关系是怎么形成的。注意这里仅仅是表明关系，查询结果并不会因为该子句发生变化。

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

这里level伪列表明了层级关系，查询结果是：

LAST_NAME	EMPLOYEE_ID	MANAGER_ID	LEVEL
Kochhar	101	100	1
Greenberg	108	101	2
Faviet	109	108	3
Popp	113	108	3

可以看到Kochhar的领导是100，因此他是level 1，Greenberg是领导是101，因此他是level 2。注意这里由于最大的领导king没有领导了，因此没有显示出来。如果要显示King的层级，则：

SELECT last_name, employee_id, manager_id, LEVEL
FROM hr.employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id

表示从root开始计算level，结果是：

LAST_NAME	EMPLOYEE_ID	MANAGER_ID	LEVEL
King	100	-	1
Kochhar	101	100	2
Greenberg	108	101	3
Faviet	109	108	4

最后一个要说明的功能是排序。除了可以使用普通的排序外，这里还支持order siblings by:

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

sibling的英文是兄弟姐妹，因此表示在同一个level下的，同一分支下的所有成员按照last_name排序。对应文章最开始的树状结构，就是2，7，9这三个人按last name排序显示(因为他们相当于兄弟姐妹)，5，6按ast name排序显示，但11不参与5，6的排序。

查询结果如下。可以看到同为level 2，Cambrault，De Haan和Errazuriz并没有显示在一起，但显然他们是有序的(c在前，然后是d，e)。而manager都是148的员工(因为在一个分支下，互为兄弟姐妹)则按照last name排序显示(由于这一支在level 3就到头了，所以这6个员工全部排在一起)。

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

观察结果的顺序，相当于从root开始遍历，遍历完一个分支之后，遍历下一个分支：最开始是king，他有三个下属：Cambrault，De Haan和Errazuriz(按last name排序)。因此从Cambrault开始遍历，紧接着是Cambrault的6个下属(都按last name排序)。由于这支到level 3就结束了，因此回到king的第二个下属De Haan，继续往下遍历，直到遍历完所有的成员。