LEVE,CONNECT_BY_ISLEAF,CONNECT_BY_ISCYCLE 伪列

1、CONNECT_BY_ISLEAF

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

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

 

首先构造一个例子:

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

说明:

  ID:节点ID

  FATHER_ID :父节点ID

  NAME :名称

表已创建。

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
  FROM T_TREE
  START WITH FATHER_ID = 0
  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
  FROM T_TREE
  START WITH ID = 7
  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 ID,  FATHER_ID,  NAME, CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF
   FROM
     (
    SELECT ROWNUM RN, ID, FATHER_ID, NAME, LEVEL LEVELS
    FROM T_TREE
    START WITH FATHER_ID = 0
    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, CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF
  FROM
  (
    SELECT ROWNUM RN, ID, FATHER_ID, NAME, LEVEL LEVELS
    FROM T_TREE
    START WITH ID = 7
    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

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

转载:https://blog.csdn.net/wanghai__/article/details/5644451

 

2、LEVE、CONNECT_BY_ISLEAF、CONNECT_BY_ISCYCLE 伪列

level 就是这个数据属于 哪一个等级,比如PRESIDENT为1,MANAGER为2

connect_by_isleaf 就是树的最末端的值,或者说这个树枝下已经没有树叶了

connect_by_iscycle 导致出现死循环的那个树枝

 

查询scott 的emp 表

SQL>

  select empno,level,rpad('*',level,'*')||ename name,job,mgr

  from emp

  start with empno=7839 connect by prior empno=mgr;
 
EMPNO     LEVEL NAME        JOB        MGR
----- ---------- ------------ --------- -----
 7839         1 *KING       PRESIDENT               --level 为1
 7566         2 **JONES     MANAGER   7839         --level 为2
 7788         3 ***SCOTT    ANALYST   7566
 7876         4 ****ADAMS   CLERK     7788
 7902         3 ***FORD     ANALYST   7566
 7369         4 ****SMITH   CLERK     7902
 7698         2 **BLAKE     MANAGER   7839
 7499         3 ***ALLEN    SALESMAN   7698
 7521         3 ***WARD     SALESMAN   7698
 7654         3 ***MARTIN   SALESMAN   7698
 7844         3 ***TURNER   SALESMAN   7698
 7900         3 ***JAMES    CLERK     7698
 7782         2 **CLARK     MANAGER   7839
 7934         3 ***MILLER   CLERK     7782
 

 

 --认为的创造一个错误的数据

update emp set mgr=7902 where empno=7839;

commit;

--现在的数据变成了

SQL> select * from emp;
 
EMPNO ENAME     JOB        MGR HIREDATE         SAL     COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH     CLERK     7902 1980-12-17    800.00              20
 7499 ALLEN     SALESMAN   7698 1981-02-20   1600.00   300.00    30
 7521 WARD      SALESMAN   7698 1981-02-22   1250.00   500.00    30
 7566 JONES     MANAGER    7839 1981-04-02   2975.00              20
 7654 MARTIN    SALESMAN   7698 1981-09-28   1250.00  1400.00    30
 7698 BLAKE     MANAGER    7839 1981-05-01   2850.00              30
 7782 CLARK     MANAGER    7839 1981-06-09   2450.00              10
 7788 SCOTT     ANALYST    7566 1987-04-19   3000.00              20
 7839 KING      PRESIDENT  7902 1981-11-17   5000.00              10
 7844 TURNER    SALESMAN   7698 1981-09-08   1500.00     0.00    30
 7876 ADAMS     CLERK     7788 1987-05-23   1100.00              20
 7900 JAMES     CLERK     7698 1981-12-03    950.00              30
 7902 FORD      ANALYST    7566 1981-12-03   3000.00              20
 7934 MILLER    CLERK     7782 1982-01-23   1300.00              10

 

 

SQL> select empno,rpad('*',level,'*')||ename name,job,mgr,sal from emp start with empno=7839 connect by prior empno=mgr;

 

select empno,rpad('*',level,'*')||ename name,job,mgr,sal from emp start with empno=7839 connect by prior empno=mgr

 

ORA-01436: 用户数据中的 CONNECT BY 循环

 

SQL> select empno,
 2        level,
 3         CONNECT_BY_ISLEAF isleaf ,
 4        connect_by_iscycle iscycle,
 5        rpad('*', level, '*') || ename name,
 6        job,
 7        mgr
 8    from emp
  9   start with empno = 7839
 10  connect by nocycle prior empno = mgr;
 
EMPNO     LEVEL    ISLEAF   ISCYCLE NAME        JOB        MGR
----- ---------- ---------- ---------- ------------ --------- -----
 7839         1         0         0 *KING       PRESIDENT  7902
 7566         2         0         0 **JONES     MANAGER   7839
 7788         3         0         0 ***SCOTT    ANALYST   7566
 7876         4         1         0 ****ADAMS   CLERK     7788
 7902         3         0         1 ***FORD     ANALYST   7566  --这一行和第一行死循环了
 7369         4         1         0 ****SMITH   CLERK     7902
 7698         2         0         0 **BLAKE     MANAGER   7839
 7499         3         1         0 ***ALLEN    SALESMAN   7698
 7521         3         1         0 ***WARD     SALESMAN   7698 --这个是树枝的末端了
 7654         3         1         0 ***MARTIN   SALESMAN   7698
 7844         3         1         0 ***TURNER   SALESMAN   7698
 7900         3         1         0 ***JAMES    CLERK     7698
 7782         2         0         0 **CLARK     MANAGER   7839
 7934         3         1         0 ***MILLER   CLERK     7782
 

转载:http://blog.sina.com.cn/s/blog_4ea0bbed0100s7w9.html

 

上一篇:Mysql数据排序


下一篇:sql字段拆分 ,连表子查询获取值