Oracle中的connect by

[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> 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!