语法:
Select ... From table_table
[Where Condition]
[[Start With Condition ] Connect By Condition]
[Order Siblings By columnn_name1,...column_nameN ]
Connect By Condition3:连接条件 Prior 指定父记录
Start With condition :限定根节点,如果省略,则表中所有行均被视为根节点
Where Condition:过滤
--1、层次查询,从根节点开始遍历
SQL> Select level,LPad(' ',(Level-1) * 4,' ') ||empno ||' '|| ename||' 'mgr
2 From scott.emp
3 Start With mgr Is Null
4 Connect By Prior empno=mgr
5 /
LEVEL MGR
---------- --------------------------------------------------------------------------------
1 7839 KING
2 7566 JONES
3 7788 SCOTT
4 7876 ADAMS
3 7902 FORD
4 7369 SMITH
2 7698 BLAKE
3 7499 ALLEN
3 7521 WARD
3 7654 MARTIN
3 7844 TURNER
3 7900 JAMES
2 7782 CLARK
3 7934 MILLER
14 rows selected
--2、省略start with condition
SQL> Select level,LPad(' ',(Level-1) * 4,' ') ||empno ||' '|| ename||' 'mgr
2 From scott.emp
3 --Start With mgr Is Null
4 Connect By Prior empno=mgr
5 /
LEVEL MGR
---------- --------------------------------------------------------------------------------
1 7788 SCOTT
2 7876 ADAMS
1 7902 FORD
2 7369 SMITH
1 7499 ALLEN
1 7900 JAMES
1 7844 TURNER
1 7654 MARTIN
1 7521 WARD
1 7934 MILLER
1 7876 ADAMS
1 7566 JONES
2 7788 SCOTT
3 7876 ADAMS
2 7902 FORD
3 7369 SMITH
1 7782 CLARK
2 7934 MILLER
1 7698 BLAKE
2 7499 ALLEN
2 7900 JAMES
2 7844 TURNER
2 7654 MARTIN
2 7521 WARD
1 7369 SMITH
1 7839 KING
2 7566 JONES
3 7788 SCOTT
4 7876 ADAMS
3 7902 FORD
4 7369 SMITH
2 7782 CLARK
3 7934 MILLER
2 7698 BLAKE
3 7499 ALLEN
3 7900 JAMES
3 7844 TURNER
3 7654 MARTIN
3 7521 WARD
39 rows selected
--3、从子节点开始遍历
SQL> Select level,LPad(' ',(Level-1) * 4,' ') ||empno ||' '|| ename||' 'mgr
2 From scott.emp
3 --Where Level<>2
4 Start With empno In(Select empno From scott.emp Where ename In('ADAMS'))
5 Connect By empno=Prior mgr
6 /
LEVEL MGR
---------- --------------------------------------------------------------------------------
1 7876 ADAMS
2 7788 SCOTT
3 7566 JONES
4 7839 KING
SQL> Select level,LPad(' ',(Level-1) * 4,' ') ||empno ||' '|| ename||' 'mgr
2 From scott.emp
3 --Where Level<>2
4 Start With empno In(Select empno From scott.emp Where ename In('ADAMS'))
5 Connect By empno=Prior mgr And Prior empno <> 7566
6 /
LEVEL MGR
---------- --------------------------------------------------------------------------------
1 7876 ADAMS
2 7788 SCOTT
3 7566 JONES
SQL> Select level,LPad(' ',(Level-1) * 4,' ') ||empno ||' '|| ename||' 'mgr
2 From scott.emp
3 --Where Level<>2
4 Start With empno In(Select empno From scott.emp Where ename In('ADAMS'))
5 Connect By empno=Prior mgr And empno <> 7566
6 /
LEVEL MGR
---------- --------------------------------------------------------------------------------
1 7876 ADAMS
2 7788 SCOTT
--4、使用Where过滤
SQL> Select level,LPad(' ',(Level-1) * 4,' ') ||empno ||' '|| ename||' 'mgr
2 From scott.emp
3 Where Level<>2
4 Start With mgr Is Null
5 Connect By Prior empno=mgr
6 /
LEVEL MGR
---------- --------------------------------------------------------------------------------
1 7839 KING
3 7788 SCOTT
4 7876 ADAMS
3 7902 FORD
4 7369 SMITH
3 7499 ALLEN
3 7521 WARD
3 7654 MARTIN
3 7844 TURNER
3 7900 JAMES
3 7934 MILLER
11 rows selected
--5、层次化排序
SQL> Select level,LPad(' ',(Level-1) * 4,' ') ||empno ||' '|| ename||' 'mgr
2 From scott.emp
3 Start With mgr Is Null
4 Connect By Prior empno=mgr
5 Order Siblings By ename
6 /
LEVEL MGR
---------- --------------------------------------------------------------------------------
1 7839 KING
2 7698 BLAKE
3 7499 ALLEN
3 7900 JAMES
3 7654 MARTIN
3 7844 TURNER
3 7521 WARD
2 7782 CLARK
3 7934 MILLER
2 7566 JONES
3 7902 FORD
4 7369 SMITH
3 7788 SCOTT
4 7876 ADAMS
14 rows selected
SQL>
Select ... From table_table
[Where Condition]
[[Start With Condition ] Connect By Condition]
[Order Siblings By columnn_name1,...column_nameN ]
Connect By Condition3:连接条件 Prior 指定父记录
Start With condition :限定根节点,如果省略,则表中所有行均被视为根节点
Where Condition:过滤
--1、层次查询,从根节点开始遍历
SQL> Select level,LPad(' ',(Level-1) * 4,' ') ||empno ||' '|| ename||' 'mgr
2 From scott.emp
3 Start With mgr Is Null
4 Connect By Prior empno=mgr
5 /
LEVEL MGR
---------- --------------------------------------------------------------------------------
1 7839 KING
2 7566 JONES
3 7788 SCOTT
4 7876 ADAMS
3 7902 FORD
4 7369 SMITH
2 7698 BLAKE
3 7499 ALLEN
3 7521 WARD
3 7654 MARTIN
3 7844 TURNER
3 7900 JAMES
2 7782 CLARK
3 7934 MILLER
14 rows selected
--2、省略start with condition
SQL> Select level,LPad(' ',(Level-1) * 4,' ') ||empno ||' '|| ename||' 'mgr
2 From scott.emp
3 --Start With mgr Is Null
4 Connect By Prior empno=mgr
5 /
LEVEL MGR
---------- --------------------------------------------------------------------------------
1 7788 SCOTT
2 7876 ADAMS
1 7902 FORD
2 7369 SMITH
1 7499 ALLEN
1 7900 JAMES
1 7844 TURNER
1 7654 MARTIN
1 7521 WARD
1 7934 MILLER
1 7876 ADAMS
1 7566 JONES
2 7788 SCOTT
3 7876 ADAMS
2 7902 FORD
3 7369 SMITH
1 7782 CLARK
2 7934 MILLER
1 7698 BLAKE
2 7499 ALLEN
2 7900 JAMES
2 7844 TURNER
2 7654 MARTIN
2 7521 WARD
1 7369 SMITH
1 7839 KING
2 7566 JONES
3 7788 SCOTT
4 7876 ADAMS
3 7902 FORD
4 7369 SMITH
2 7782 CLARK
3 7934 MILLER
2 7698 BLAKE
3 7499 ALLEN
3 7900 JAMES
3 7844 TURNER
3 7654 MARTIN
3 7521 WARD
39 rows selected
--3、从子节点开始遍历
SQL> Select level,LPad(' ',(Level-1) * 4,' ') ||empno ||' '|| ename||' 'mgr
2 From scott.emp
3 --Where Level<>2
4 Start With empno In(Select empno From scott.emp Where ename In('ADAMS'))
5 Connect By empno=Prior mgr
6 /
LEVEL MGR
---------- --------------------------------------------------------------------------------
1 7876 ADAMS
2 7788 SCOTT
3 7566 JONES
4 7839 KING
SQL> Select level,LPad(' ',(Level-1) * 4,' ') ||empno ||' '|| ename||' 'mgr
2 From scott.emp
3 --Where Level<>2
4 Start With empno In(Select empno From scott.emp Where ename In('ADAMS'))
5 Connect By empno=Prior mgr And Prior empno <> 7566
6 /
LEVEL MGR
---------- --------------------------------------------------------------------------------
1 7876 ADAMS
2 7788 SCOTT
3 7566 JONES
SQL> Select level,LPad(' ',(Level-1) * 4,' ') ||empno ||' '|| ename||' 'mgr
2 From scott.emp
3 --Where Level<>2
4 Start With empno In(Select empno From scott.emp Where ename In('ADAMS'))
5 Connect By empno=Prior mgr And empno <> 7566
6 /
LEVEL MGR
---------- --------------------------------------------------------------------------------
1 7876 ADAMS
2 7788 SCOTT
--4、使用Where过滤
SQL> Select level,LPad(' ',(Level-1) * 4,' ') ||empno ||' '|| ename||' 'mgr
2 From scott.emp
3 Where Level<>2
4 Start With mgr Is Null
5 Connect By Prior empno=mgr
6 /
LEVEL MGR
---------- --------------------------------------------------------------------------------
1 7839 KING
3 7788 SCOTT
4 7876 ADAMS
3 7902 FORD
4 7369 SMITH
3 7499 ALLEN
3 7521 WARD
3 7654 MARTIN
3 7844 TURNER
3 7900 JAMES
3 7934 MILLER
11 rows selected
--5、层次化排序
SQL> Select level,LPad(' ',(Level-1) * 4,' ') ||empno ||' '|| ename||' 'mgr
2 From scott.emp
3 Start With mgr Is Null
4 Connect By Prior empno=mgr
5 Order Siblings By ename
6 /
LEVEL MGR
---------- --------------------------------------------------------------------------------
1 7839 KING
2 7698 BLAKE
3 7499 ALLEN
3 7900 JAMES
3 7654 MARTIN
3 7844 TURNER
3 7521 WARD
2 7782 CLARK
3 7934 MILLER
2 7566 JONES
3 7902 FORD
4 7369 SMITH
3 7788 SCOTT
4 7876 ADAMS
14 rows selected
SQL>