SELECT [LEVEL] ,column,expression,...
FROM table_name
[WHERE where_clause]
[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

--使用start with ...connect by prior 从根节点开始遍历
SQL>select empno,mgr,ename,job from emp
2  start with empno = 7839
3  connect by prior empno = mgr;
EMPNO        MGR ENAME      JOB
---------- ---------- ---------- ---------
7839            KING       PRESIDENT
7566       7839 JONES      MANAGER
7788       7566 SCOTT      ANALYST
7876       7788 ADAMS      CLERK
7902       7566 FORD       ANALYST
7369       7902 SMITH      CLERK
7698       7839 BLAKE      MANAGER
7499       7698 ALLEN      SALESMAN
7521       7698 WARD       SALESMAN
7654       7698 MARTIN     SALESMAN
7844       7698 TURNER     SALESMAN
EMPNO        MGR ENAME      JOB
---------- ---------- ---------- ---------
7900       7698 JAMES      CLERK
7782       7839 CLARK      MANAGER
7934       7782 MILLER     CLERK
14 rows selected.

--伪列level的使用
--注意connect by prior empno = mgr 的理解
--prior表示前一条记录,即下一条返回记录的mgr应当等于前一条记录的empno
SQL>selectlevel,empno,mgr,ename,job from emp
2  start with ename ='KING'
3  connect by prior empno = mgr
4  orderbylevel;
LEVEL      EMPNO        MGR ENAME      JOB
---------- ---------- ---------- ---------- ---------
1       7839            KING       PRESIDENT
2       7566       7839 JONES      MANAGER
2       7698       7839 BLAKE      MANAGER
2       7782       7839 CLARK      MANAGER
3       7902       7566 FORD       ANALYST
3       7521       7698 WARD       SALESMAN
3       7900       7698 JAMES      CLERK
3       7934       7782 MILLER     CLERK
3       7499       7698 ALLEN      SALESMAN
3       7788       7566 SCOTT      ANALYST
3       7654       7698 MARTIN     SALESMAN
LEVEL      EMPNO        MGR ENAME      JOB
---------- ---------- ---------- ---------- ---------
3       7844       7698 TURNER     SALESMAN
4       7876       7788 ADAMS      CLERK
4       7369       7902 SMITH      CLERK
--获得层次数
SQL>selectcount(distinctlevel) "Level" from emp
2  start with ename ='KING'
3  connect by prior empno = mgr;
Level
----------
4
--格式化层次查询结果(使用左填充* level - 1个空格)
SQL> col Ename for a30
SQL>selectlevel,
2    lpad(' ',2 *level- 1)  ename as "Ename",
3    job
4  from emp
5  start with ename ='KING'
6  connect by prior empno = mgr;
LEVEL Ename                          JOB
---------- ------------------------------ ---------
1  KING                          PRESIDENT
2    JONES                       MANAGER
3      SCOTT                     ANALYST
4        ADAMS                   CLERK
3      FORD                      ANALYST
4        SMITH                   CLERK
2    BLAKE                       MANAGER
3      ALLEN                     SALESMAN
3      WARD                      SALESMAN
3      MARTIN                    SALESMAN
3      TURNER                    SALESMAN
LEVEL Ename                          JOB
---------- ------------------------------ ---------
3      JAMES                     CLERK
2    CLARK                       MANAGER
3      MILLER                    CLERK
14 rows selected.
--从非根节点开始遍历(只需修改start with 中的条件即可)
SQL>selectlevel,
2    lpad(' ',2 *level- 1)  ename as "Ename",
3    job
4  from emp
5  start with ename ='SCOTT'
6  connect by prior empno = mgr;
LEVEL Ename                          JOB
---------- ------------------------------ ---------
1  SCOTT                         ANALYST
2    ADAMS                       CLERK
--从下向上遍历(交换connect by prior中的条件即可,使用mgr = empno)

--注意connect by prior mgr = empno 的理解
--prior表示前一条记录,即下一条返回记录的empno应当等于前一条记录的mgr

SQL>selectlevel,
2    lpad(' ',2 *level- 1)  ename as "Ename",
3    job
4  from emp
5  start with ename ='SCOTT'
6  connect by prior mgr = empno;
LEVEL Ename                          JOB
---------- ------------------------------ ---------
1  SCOTT                         ANALYST
2    JONES                       MANAGER
3      KING                      PRESIDENT
--从下向上遍历(也可以将prior置于等号右边,得到相同的结果)
SQL>selectlevel,
2    lpad(' ',2 *level- 1)  ename as "Ename",
3    job
4  from emp
5  start with ename ='SCOTT'
6  connect by empno = prior mgr;
LEVEL Ename                          JOB
---------- ------------------------------ ---------
1  SCOTT                         ANALYST
2    JONES                       MANAGER
3      KING                      PRESIDENT
--从层次查询中删除节点和分支
SQL>selectlevel,
2    lpad(' ',2 *level- 1)  ename as "Ename"
3    ,job
4  from emp
5  where ename !='SCOTT'--通过where子句来过滤SCOTT用户，但SCOTT的下属ADAMS并没有过滤掉
6  start with empno = 7839
7  connect by prior empno = mgr;
LEVEL Ename                JOB
---------- -------------------- ---------
1  KING                PRESIDENT
2    JONES             MANAGER
4        ADAMS         CLERK
3      FORD            ANALYST
4        SMITH         CLERK
2    BLAKE             MANAGER
3      ALLEN           SALESMAN
3      WARD            SALESMAN
3      MARTIN          SALESMAN
3      TURNER          SALESMAN
3      JAMES           CLERK
LEVEL Ename                JOB
---------- -------------------- ---------
2    CLARK             MANAGER
3      MILLER          CLERK
13 rows selected.
--通过将过滤条件由where 子句的内容移动到connect by prior 子句中过滤掉SCOTT及其下属
SQL>selectlevel,
2    lpad(' ',2 *level- 1)  ename as "Ename"
3    ,job
4  from emp
5  start with empno = 7839
6  connect by prior empno = mgr and ename !='SCOTT';
LEVEL Ename                JOB
---------- -------------------- ---------
1  KING                PRESIDENT
2    JONES             MANAGER
3      FORD            ANALYST
4        SMITH         CLERK
2    BLAKE             MANAGER
3      ALLEN           SALESMAN
3      WARD            SALESMAN
3      MARTIN          SALESMAN
3      TURNER          SALESMAN
3      JAMES           CLERK
2    CLARK             MANAGER
LEVEL Ename                JOB
---------- -------------------- ---------
3      MILLER          CLERK
12 rows selected.
--在层次化查询中增加过滤条件或使用子查询
SQL>selectlevel,
2    lpad(' ',2 *level- 1)  ename as "Ename"
3    ,job
4  from emp
5  where sal > 2500
6  start with empno = 7839
7  connect by prior empno = mgr
8  ;
LEVEL Ename                JOB
---------- -------------------- ---------
1  KING                PRESIDENT
2    JONES             MANAGER
3      SCOTT           ANALYST
3      FORD            ANALYST
2    BLAKE             MANAGER
SQL>selectlevel,
2    lpad(' ',2 *level- 1)  ename as "Ename"
3    ,job
4  from emp
5  where sal >(selectavg(sal)from emp)
6  start with empno = 7839
7  connect by prior empno = mgr ;
LEVEL Ename                JOB
---------- -------------------- ---------
1  KING                PRESIDENT
2    JONES             MANAGER
3      SCOTT           ANALYST
3      FORD            ANALYST
2    BLAKE             MANAGER
2    CLARK             MANAGER
6 rows selected.

--转自