本文系转载，如果有侵犯您权益的地方，烦请即使告知
1、一个比较难的SQL（Oracle 9i 实现 10G 的 CONNECT_BY_ISLEAF、CONNECT_BY_ROOT 等功能）
参考网址：http://blog.csdn.net/wzy0623/article/details/1644049
有一个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 7 INSERT INTO emp 8 (emp_id, manager_id 9 ) 10 VALUES ('001', '101' 11 ); 12 INSERT INTO emp 13 (emp_id, manager_id 14 ) 15 VALUES ('001', '102' 16 ); 17 INSERT INTO emp 18 (emp_id, manager_id 19 ) 20 VALUES ('101', '201' 21 ); 22 INSERT INTO emp 23 (emp_id, manager_id 24 ) 25 VALUES ('102', '202' 26 ); 27 INSERT INTO emp 28 (emp_id, manager_id 29 ) 30 VALUES ('002', '102' 31 ); 32 INSERT INTO emp 33 (emp_id, manager_id 34 ) 35 VALUES ('003', '103' 36 ); 37 INSERT INTO emp 38 (emp_id, manager_id 39 ) 40 VALUES ('103', '203' 41 ); 42 INSERT INTO emp 43 (emp_id, manager_id 44 ) 45 VALUES ('201', '301' 46 ); 47 INSERT INTO emp 48 (emp_id, manager_id 49 ) 50 VALUES ('203', '303' 51 ); 52 COMMIT ;
有人给出这样的答案：
1 SELECT emp_id, 2 manager_id 3 FROM (SELECT connect_by_root(emp_id) emp_id, 4 manager_id, 5 connect_by_isleaf v_isleaf 6 FROM emp 7 CONNECT BY emp_id = PRIOR manager_id) 8 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, 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)) 8 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, 6 manager_id, 7 LEVEL lev, 8 (rownum - LEVEL) part 9 FROM emp 10 CONNECT BY emp_id = PRIOR manager_id);
按 part 分区，以 lev 正排序，再使用 FIRST_VALUE 操作使第一列都显示叶子节点；
按 part 分区，以 lev 倒排序，再使用 ROW_NUMBER () 函数划分等级，等级排第一的即为根节点。
最外层嵌套查询，取得所要求的结果输出。