使用分析函数实现Oracle 10G提供的CONNECT_BY_ISLEAF和CONNECT_BY_ROOT的功能（转载）
文章转载至：http://blog.csdn.net/wzy0623/article/details/1644049
如果，有侵犯您权益的地方，烦请及时的告知我，我会即刻停止侵权行为
Oracle 10g提供了几个函数：CONNECT_BY_ISLEAF、CONNECT_BY_ROOT、CONNECT_BY_PATH，对树有了更加强大的支持，但是在10g之前，我们没有这些函数，
该如何实现CONNECT_BY_ISLEAF、CONNECT_BY_ROOT这个函数的功能，下面我们介绍下使用分析函数，来解决该问题。
有一个emp表，2个字段，员工id和主管id. 1，emp_id, 2,manager_id
假如有以下资料,一个员工可以对应一个或多个主管id,即一个员工可能有几个主管。
emp_id manager_id
001 101
001 102
101 201
102 202
002 102
003 103
103 203
201 301
203 303
现在要通过任何一个员工id,能查到他的最高主管的id，可能结果不止一笔。
即如果是001，则结果如下：
emp_id manager_id
001 301
001 202
........................
即如果是002，则结果如下：
emp_id manager_id
002 102
........................
即如果是103，则结果如下：
emp_id manager_id
103 303
建表语句：
1 CREATE TABLE emp 2 ( 3 emp_id VARCHAR2(10 ), 4 manager_id VARCHAR2(10 ) 5 ); 6 INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('001', '101'); 7 INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('001', '102'); 8 INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('101', '201'); 9 INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('102', '202'); 10 INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('002', '102'); 11 INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('003', '103'); 12 INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('103', '203'); 13 INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('201', '301'); 14 INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('203', '303'); 15 COMMIT;
在Oracle 10G中可以通过如下语句去实现：
1 SELECT EMP_ID, MANAGER_ID 2 FROM (SELECT CONNECT_BY_ROOT(EMP_ID) EMP_ID, 3 MANAGER_ID, 4 CONNECT_BY_ISLEAF V_ISLEAF 5 FROM EMP 6 CONNECT BY EMP_ID = PRIOR MANAGER_ID) 7 WHERE V_ISLEAF = 1
这个写法非常简洁，用到了10G connect by 增强的特性，如判断是否叶子节点的伪列 CONNECT_BY_ISLEAF，只使用根行返回结果的一元操作符 CONNECT_BY_ROOT 等，很好。但提问者说使用的是 9i，这就有些麻烦了，能否使用一个 sql 而不是 plsql 实现呢？深入研究后给出了我的 sql：
如下：
1 SELECT EMP_ID, MANAGER_ID 2 FROM (SELECT FIRST_VALUE(EMP_ID) OVER(PARTITION BY PART ORDER BY LEV) EMP_ID, 3 ROW_NUMBER() OVER(PARTITION BY PART ORDER BY LEV DESC) RN, 4 PART, 5 MANAGER_ID AS MANAGER_ID 6 FROM (SELECT EMP_ID, MANAGER_ID, LEVEL LEV, (ROWNUM - LEVEL) PART 7 FROM EMP 8 CONNECT BY EMP_ID = PRIOR MANAGER_ID)) 9 WHERE RN = 1;
9i 没有提供 CONNECT_BY_ISLEAF 及 CONNECT_BY_ROOT，但可以使用分析函数实现其基本功能，下面分析一下。
最内层的查询：
1 SELECT EMP_ID, MANAGER_ID, LEVEL LEV, (ROWNUM - LEVEL) PART 2 FROM EMP 3 CONNECT BY EMP_ID = PRIOR MANAGER_ID;
这里用到了从叶子到跟的反向遍历，同时用 (ROWNUM - LEVEL) part 列的值表示一个从叶子到根的路径，为使用分析函数的分区条件做准备。
二层嵌套查询
1 SELECT FIRST_VALUE(EMP_ID) OVER(PARTITION BY PART ORDER BY LEV) EMP_ID, 2 ROW_NUMBER() OVER(PARTITION BY PART ORDER BY LEV DESC) RN, 3 PART, 4 MANAGER_ID AS MANAGER_ID 5 FROM (SELECT EMP_ID, MANAGER_ID, LEVEL LEV, (ROWNUM - LEVEL) PART 6 FROM EMP 7 CONNECT BY EMP_ID = PRIOR MANAGER_ID);
按 part 分区，以 lev 正排序，再使用 FIRST_VALUE 操作使第一列都显示叶子节点；
按 part 分区，以 lev 倒排序，再使用 ROW_NUMBER () 函数划分等级，等级排第一的即为根节点。
