Oracle中的connect by

最新推荐文章于 2023-11-01 13:15:10 发布

msdnchina

最新推荐文章于 2023-11-01 13:15:10 发布

阅读量967

点赞数

分类专栏： Oracle DB 文章标签： connect by

本文链接：https://blog.csdn.net/msdnchina/article/details/53244672

版权

Oracle DB 专栏收录该内容

773 篇文章 3 订阅

订阅专栏

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 20 14:09:35 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col empno format a18  
SQL> set lines 300
SQL> set pages 30
SQL> select lpad(' ', level * 2 - 1, ' ') || xxx.empno empno,
  2         xxx.ename,
  3         xxx.job,
  4         xxx.mgr,
  5         xxx.sal
  6    from (select empno, ename, job, mgr, sal from scott.emp) xxx
  7  connect by prior xxx.empno = xxx.mgr
  8   start with xxx.mgr is null --->>>从 xxx.mgr is null的那一行开始.
  9   order siblings by xxx.empno;------------------------>>>>亲兄弟排序(7566和7698是亲兄弟,而7876和7369顶多算是堂兄弟)
 
EMPNO		   ENAME        JOB	         MGR	 SAL
-----------   ---------- --------- ---------- ----------
 7839		   KING       PRESIDENT 			 5000
   7566 	   JONES      MANAGER	      7839	 2975  --->>>prior xxx.empno = xxx.mgr 的解释:找到mgr为7566的那一个empno 7788,并把empno7788放在7566的下一行.
     7788	   SCOTT      ANALYST	      7566	 3000
       7876	   ADAMS      CLERK	          7788	 1100
     7902	   FORD       ANALYST	      7566	 3000
       7369	   SMITH      CLERK	          7902	  800
   7698 	   BLAKE      MANAGER	      7839	 2850
     7499	   ALLEN      SALESMAN	      7698	 1600
     7521	   WARD       SALESMAN	      7698	 1250
     7654	   MARTIN     SALESMAN	      7698	 1250
     7844	   TURNER     SALESMAN	      7698	 1500
     7900	   JAMES      CLERK	          7698	  950
   7782 	   CLARK      MANAGER	      7839	 2450
     7934	   MILLER     CLERK	          7782	 1300

14 rows selected.

SQL>