原帖见于 http://blog.csdn.net/wzy0623/archive/2007/06/08/1644049.aspx
Oracle我没用过,这里提供一种MSSQL的实现方法,如果存在级数大于3的话,可以根据需要增加左连接的方法实现,首先建立所需要的资料表,执行一下语句可以实现对所需要数据的查询。
select f.emp_id ,
case
when f.aemp_id is null then f.manager_id
when f.aemp_id is not null and f.bemp_id is null then f.amanager_id
else f.bmanager_id
end 'manager_id'
from
(
select emp.*,s.* from emp left join
(select a.emp_id aemp_id,
a.manager_id amanager_id,
b.emp_id bemp_id ,
b.manager_id bmanager_id
from emp a left join emp b on a.manager_id=b.emp_id) s
on emp.manager_id=s.aemp_id
) f
有一个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
......................................
以下是建表脚本及资料:
(
emp_id VARCHAR2 ( 10 ),
manager_id VARCHAR2 ( 10 )
);
INSERT INTO emp
(emp_id, manager_id
)
VALUES ( ' 001 ' , ' 101 '
);
INSERT INTO emp
(emp_id, manager_id
)
VALUES ( ' 001 ' , ' 102 '
);
INSERT INTO emp
(emp_id, manager_id
)
VALUES ( ' 101 ' , ' 201 '
);
INSERT INTO emp
(emp_id, manager_id
)
VALUES ( ' 102 ' , ' 202 '
);
INSERT INTO emp
(emp_id, manager_id
)
VALUES ( ' 002 ' , ' 102 '
);
INSERT INTO emp
(emp_id, manager_id
)
VALUES ( ' 003 ' , ' 103 '
);
INSERT INTO emp
(emp_id, manager_id
)
VALUES ( ' 103 ' , ' 203 '
);
INSERT INTO emp
(emp_id, manager_id
)
VALUES ( ' 201 ' , ' 301 '
);
INSERT INTO emp
(emp_id, manager_id
)
VALUES ( ' 203 ' , ' 303 '
);
COMMIT ;
有人给出这样的答案:
FROM ( SELECT CONNECT_BY_ROOT (emp_id) emp_id, manager_id,
CONNECT_BY_ISLEAF v_isleaf
FROM emp
CONNECT BY emp_id = PRIOR manager_id)
WHERE v_isleaf = 1 ;
这个写法非常简洁,用到了10G connect by 增强的特性,如判断是否叶子节点的伪列 CONNECT_BY_ISLEAF,只使用根行返回结果的一元操作符 CONNECT_BY_ROOT 等,很好。但提问者说使用的是 9i,这就有些麻烦了,能否使用一个 sql 而不是 plsql 实现呢?深入研究后给出了我的 sql:
FROM ( SELECT FIRST_VALUE (emp_id) OVER (PARTITION BY part ORDER BY lev) emp_id,
ROW_NUMBER () OVER (PARTITION BY part ORDER BY lev DESC ) rn,
part, manager_id AS manager_id
FROM ( SELECT emp_id, manager_id, LEVEL lev, (ROWNUM - LEVEL ) part
FROM emp
CONNECT BY emp_id = PRIOR manager_id))
WHERE rn = 1 ;
9i 没有提供 CONNECT_BY_ISLEAF 及 CONNECT_BY_ROOT,但可以使用分析函数实现其基本功能,下面分析一下。
最内层的查询:
SELECT emp_id, manager_id, LEVEL lev, (ROWNUM - LEVEL) part
FROM emp
CONNECT BY emp_id = PRIOR manager_id;
这里用到了从叶子到跟的反向遍历,同时用 (ROWNUM - LEVEL) part 列的值表示一个从叶子到根的路径,为使用分析函数的分区条件做准备。
二层嵌套查询
SELECT FIRST_VALUE (emp_id) OVER (PARTITION BY part ORDER BY lev) emp_id,
ROW_NUMBER () OVER (PARTITION BY part ORDER BY lev DESC) rn, part,
manager_id AS manager_id
FROM (SELECT emp_id, manager_id, LEVEL lev, (ROWNUM - LEVEL) part
FROM emp
CONNECT BY emp_id = PRIOR manager_id);
按 part 分区,以 lev 正排序,再使用 FIRST_VALUE 操作使第一列都显示叶子节点;
按 part 分区,以 lev 倒排序,再使用 ROW_NUMBER () 函数划分等级,等级排第一的即为根节点。
最外层嵌套查询,取得所要求的结果输出。