使用分析函数实现Oracle 10G提供的CONNECT_BY_ISLEAF和CONNECT_BY_ROOT的功能(转载)

时间:2021-02-14 04:43:41

文章转载至: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

建表语句:

 CREATE TABLE emp
(
emp_id VARCHAR2(10 ),
manager_id VARCHAR2(10 )
);
INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('', '');
INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('', '');
INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('', '');
INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('', '');
INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('', '');
INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('', '');
INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('', '');
INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('', '');
INSERT INTO EMP (EMP_ID, MANAGER_ID) VALUES ('', '');
COMMIT;

在Oracle 10G中可以通过如下语句去实现:

 SELECT EMP_ID, MANAGER_ID
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:

如下:

 SELECT EMP_ID, MANAGER_ID
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 () 函数划分等级,等级排第一的即为根节点。