ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10g树形查询新特性CONNECT_BY_ISLEAF的9i实现方式

10g树形查询新特性CONNECT_BY_ISLEAF的9i实现方式

原创 Linux操作系统 作者:yangtingkun 时间:2008-07-17 22:57:54 0 删除 编辑

在10g中Oracle提供了新的伪列:CONNECT_BY_ISLEAF,通过这个伪列,可以判断当前的记录是否是树的叶节点。

这里描述一下在9i中如何实现相应的功能。

 

 

首先构造一个例子:

SQL> CREATE TABLE T_TREE (ID NUMBER PRIMARY KEY, FATHER_ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO T_TREE VALUES (1, 0, 'A');

已创建 1 行。

SQL> INSERT INTO T_TREE VALUES (2, 1, 'BC');

已创建 1 行。

SQL> INSERT INTO T_TREE VALUES (3, 1, 'DE');

已创建 1 行。

SQL> INSERT INTO T_TREE VALUES (4, 1, 'FG');

已创建 1 行。

SQL> INSERT INTO T_TREE VALUES (5, 2, 'HIJ');

已创建 1 行。

SQL> INSERT INTO T_TREE VALUES (6, 4, 'KLM');

已创建 1 行。

SQL> INSERT INTO T_TREE VALUES (7, 6, 'NOPQ');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM T_TREE;

        ID  FATHER_ID NAME
---------- ---------- ------------------------------
         1          0 A
         2          1 BC
         3          1 DE
         4          1 FG
         5          2 HIJ
         6          4 KLM
         7          6 NOPQ

已选择7行。

下面看看CONNECT_BY_ISLEAF的功能:

SQL> SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAF LEAF
  2  FROM T_TREE
  3  START WITH FATHER_ID = 0
  4  CONNECT BY PRIOR ID = FATHER_ID;

        ID  FATHER_ID NAME                                 LEAF
---------- ---------- ------------------------------ ----------
         1          0 A                                       0
         2          1 BC                                      0
         5          2 HIJ                                     1
         3          1 DE                                      1
         4          1 FG                                      0
         6          4 KLM                                     0
         7          6 NOPQ                                    1

已选择7行。

SQL> SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAF LEAF
  2  FROM T_TREE
  3  START WITH ID = 7
  4  CONNECT BY PRIOR FATHER_ID = ID;

        ID  FATHER_ID NAME                                 LEAF
---------- ---------- ------------------------------ ----------
         7          6 NOPQ                                    0
         6          4 KLM                                     0
         4          1 FG                                      0
         1          0 A                                       1

CONNECT_BY_ISLEAF可以判断当前记录是否是树的叶节点。而这个功能在9i中没有简单的方法来实现,只能通过分析函数来进行判断:

SQL> SELECT
  2   ID,
  3   FATHER_ID,
  4   NAME,
  5   CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF
  6  FROM
  7  (
  8   SELECT ROWNUM RN, ID, FATHER_ID, NAME, LEVEL LEVELS
  9   FROM T_TREE
 10   START WITH FATHER_ID = 0
 11   CONNECT BY PRIOR ID = FATHER_ID
 12  );

        ID  FATHER_ID NAME                                 LEAF
---------- ---------- ------------------------------ ----------
         1          0 A                                       0
         2          1 BC                                      0
         5          2 HIJ                                     1
         3          1 DE                                      1
         4          1 FG                                      0
         6          4 KLM                                     0
         7          6 NOPQ                                    1

已选择7行。

SQL> SELECT
  2   ID,
  3   FATHER_ID,
  4   NAME,
  5   CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF
  6  FROM
  7  (
  8   SELECT ROWNUM RN, ID, FATHER_ID, NAME, LEVEL LEVELS
  9   FROM T_TREE
 10   START WITH ID = 7
 11   CONNECT BY PRIOR FATHER_ID = ID
 12  );

        ID  FATHER_ID NAME                                 LEAF
---------- ---------- ------------------------------ ----------
         7          6 NOPQ                                    0
         6          4 KLM                                     0
         4          1 FG                                      0
         1          0 A                                       1

利用分析函数可以相对简单的在9i实现CONNECT_BY_ISLEAF伪列的功能。

 

 

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/4227/viewspace-402910/ ,如需转载,请注明出处,否则将追究法律责任。

下一篇: ORACLE11G权威指南
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1964
  • 访问量
    11085795