一、connect by
1、语法
select .... from tab_name
[where .....]
start with ....
connect by prior father = son 或者 son = father
2、讲解
(1)start with指从什么地方开始遍历
(2)connect by prior指从上往下还是从下往上
如果是father = son 那么指的就是从下往上遍历,即从儿子找老子
如果是son = father 那么指的是从上往下遍历,即从老子找儿子
3、举例
(1)从上往下找son = father
SQL> select level, t.empno, t.ename, t.job, t.mgr
2 from emp t
3 start with t.empno = 7788
4 connect by prior t.empno = t.mgr
5 order by level;
LEVEL EMPNO ENAME JOB MGR
---------- ----- ---------- --------- -----
1 7788 SCOTT ANALYST 7566
2 7876 ADAMS CLERK 7788
(2)从下往上找father = son
SQL> select level, t.empno, t.ename, t.job, t.mgr
2 from emp t
3 start with t.empno = 7788
4 connect by prior t.mgr = t.empno
5 order by level;
LEVEL EMPNO ENAME JOB MGR
---------- ----- ---------- --------- -----
1 7788 SCOTT ANALYST 7566
2 7566 JONES MANAGER 7839
3 7839 KING PRESIDENT