oracle connet by,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>

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。