1、connect by 处理上下级关系

SQL> select empno,ename,job,mgr,deptno,level from emp start with mgr is null connect by prior empno=mgr;

     EMPNO ENAME      JOB              MGR     DEPTNO      LEVEL
---------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT                    10          1
      7566 JONES      MANAGER         7839         20          2
      7788 SCOTT      ANALYST         7566         20          3
      7876 ADAMS      CLERK           7788         20          4
      7902 FORD       ANALYST         7566         20          3
      7369 SMITH      CLERK           7902         20          4
      7698 BLAKE      MANAGER         7839         30          2
      7499 ALLEN      SALESMAN        7698         30          3
      7521 WARD       SALESMAN        7698         30          3
      7654 MARTIN     SALESMAN        7698         30          3
      7844 TURNER     SALESMAN        7698         30          3

EMPNO ENAME JOB MGR DEPTNO LEVEL
---------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 30 3

level关键字要存在，start with代表以哪个为头，connect by prior代表上一条记录是本条记录的父亲。

SYS_CONNECT_BY_PATH与connect by效果一样，只不过有连接符

SQL> select empno,deptno,sys_connect_by_path(ename,'>') "PATH" from emp start with mgr is null connect by prior empno=mgr;

     EMPNO     DEPTNO PATH
---------- ---------- ------------------------------
      7839         10 >KING
      7566         20 >KING>JONES
      7788         20 >KING>JONES>SCOTT
      7876         20 >KING>JONES>SCOTT>ADAMS
      7902         20 >KING>JONES>FORD
      7369         20 >KING>JONES>FORD>SMITH
      7698         30 >KING>BLAKE
      7499         30 >KING>BLAKE>ALLEN
      7521         30 >KING>BLAKE>WARD
      7654         30 >KING>BLAKE>MARTIN
      7844         30 >KING>BLAKE>TURNER

     EMPNO     DEPTNO PATH
---------- ---------- ------------------------------
      7900         30 >KING>BLAKE>JAMES
      7782         10 >KING>CLARK
      7934         10 >KING>CLARK>MILLER

ITPub博客

connect by学习

最新文章