sql 层次化查询(START BY ... CONNECT BY PRIOR)
SQL> select level,empno,mgr,ename,job from emp2start with ename = 'KING'
3connect by prior empno = mgr
4order by level;
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> select count(distinct level) "Level" from emp
2start with ename = 'KING'
3connect by prior empno = mgr;
Level
----------
4
--格式化层次查询结果(使用左填充* level - 1个空格)
SQL> col Ename for a30
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename",
3 job
4from emp
5start with ename = 'KING'
6connect by prior empno = mgr;
LEVEL Ename JOB
---------- ------------------------------ ---------
1KING 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
页:
[1]