3.家族树
语法:
select column from table_name start with column=value
connect by prior 父主键=子主键
3.1排除单一性和分枝
以ORACLE中的EMP表为例
[例]从顶到底列出各雇员的信息
SQL> select lpad(' ',4*(level-1))||ename name,empno,mgr from emp start with mgr is null
2 connect by prior empno=mgr;
NAME EMPNO MGR
--------- --------- ---------
KING 7839
JONES 7566 7839
SCOTT 7788 7566
ADAMS 7876 7788
3.2遍历至根
[例1]现在要从某个雇员开始向他的上级列出该雇员的层次结构
SQL> col ename for a30;
SQL> select lpad(' ',4*(level-1))||ename ename,mgr,empno from scott.emp
2 start with mgr=7788 connect by prior mgr=empno;
ENAME MGR EMPNO
------------------------------ --------- ---------
ADAMS 7788 7876
SCOTT 7566 7788
JONES 7839 7566
KING 7839
[例2]列出所有雇员的层次结构
SQL> select lpad(' ',4*(level-1))||ename ename,empno,mgr from scott.emp
2 start with mgr is not null
3 connect by empno=prior mgr;
ENAME EMPNO MGR
------------------------------ --------- ---------
SMITH 7369 7902
FORD 7902 7566
JONES 7566 7839
KING 7839
ALLEN 7499 7698
BLAKE 7698 7839
KING 7839
WARD 7521 7698
BLAKE 7698 7839
KING 7839
JONES 7566 7839
KING 7839
MARTIN 7654 7698
BLAKE 7698 7839
KING 7839
BLAKE 7698 7839
KING 7839
CLARK 7782 7839
KING 7839
SCOTT 7788 7566
JONES 7566 7839