普通的级别查询方式:
SQL> select deptno,empno,ename from emp
2 start with empno = 7839
3 connect by prior empno = mgr
4 order by deptno,empno;
DEPTNO EMPNO ENAME
---------- ---------- --------------------------------------------------------------------------------
10 7782 CLARK
10 7839 KING
10 7934 MILLER
20 23 connie
20 7369 SMITH
20 7566 JONES
20 7788 SCOTT
20 7876 ADAMS
20 7902 FORD
30 23 conn
30 7499 ALLEN
DEPTNO EMPNO ENAME
---------- ---------- --------------------------------------------------------------------------------
30 7521 WARD
30 7654 MARTIN
30 7698 BLAKE
30 7844 TURNER
30 7900 JAMES
已选择16行。
sys_connect_by_path连接:必须有connct by
SQL> select empno,sys_connect_by_path(ename,' -> ')path_name
2 from emp
3 start with empno = 7839
4 connect by prior empno = mgr;
EMPNO PATH_NAME
---------- --------------------------------------------------------------------------------
7839 -> KING
7566 -> KING -> JONES
7788 -> KING -> JONES -> SCOTT
7876 -> KING -> JONES -> SCOTT -> ADAMS
7902 -> KING -> JONES -> FORD
23 -> KING -> JONES -> FORD -> conn
7369 -> KING -> JONES -> FORD -> SMITH
23 -> KING -> JONES -> FORD -> connie
7698 -> KING -> BLAKE
7499 -> KING -> BLAKE -> ALLEN
7521 -> KING -> BLAKE -> WARD
EMPNO PATH_NAME
---------- --------------------------------------------------------------------------------
7654 -> KING -> BLAKE -> MARTIN
7844 -> KING -> BLAKE -> TURNER
7900 -> KING -> BLAKE -> JAMES
7782 -> KING -> CLARK
7934 -> KING -> CLARK -> MILLER
已选择16行。
wmsys.wm_concat:
SQL> select wmsys.wm_concat(ename)path_name from emp;
PATH_NAME
--------------------------------------------------------------------------------
conn,SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FOR
D,MILLER,connie
SQL> select wmsys.wm_concat(ename)path_name from emp
2 start with empno = 7839
3 connect by prior empno = mgr;
PATH_NAME
--------------------------------------------------------------------------------
KING,JONES,SCOTT,ADAMS,FORD,conn,SMITH,connie,BLAKE,ALLEN,WARD,MARTIN,TURNER,JAM
ES,CLARK,MILLER
查看部门下所有的雇员:
SQL> select deptno,max(sys_connect_by_path(ename,' -> ')) path_name
2 from (select deptno,empno,ename,
3
row_number()over(partition by deptno order by empno)rank
4
from emp) a
5 start with a.rank = 1
6 connect by prior a.rank = a.rank - 1 and prior a.deptno = a.deptno
7 group by deptno;
DEPTNO PATH_NAME
---------- --------------------------------------------------------------------------------
10 -> CLARK -> KING -> MILLER
20 -> connie -> SMITH -> JONES -> SCOTT -> ADAMS -> FORD
30 -> conn -> ALLEN -> WARD -> MARTIN -> BLAKE -> TURNER -> JAMES
null ->
查看部门下所有的雇员:
SQL> select deptno,wmsys.wm_concat(ename)path_name
2 from emp
3 group by deptno;
DEPTNO PATH_NAME
---------- --------------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,ADAMS,connie,FORD,SCOTT,JONES
30 conn,MARTIN,BLAKE,WARD,JAMES,TURNER,ALLEN
null null
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17032106/viewspace-714172/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17032106/viewspace-714172/