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