SELECT EMPNO,ENAME，MGR
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
START WITH ENAME=’KING’;
EMPNO ENAME MGR
-------------------------------------------------------------------------------------
7839 KING
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7698 BLAKE 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
7782 CLARK 7839
7934 MILLER 7782
14 rows selected.
但我想要这种效果,可以达到吗?
比如 7934 miller这个人 我要知道他的上级 一直到根节点 每个节点为一列
------------------------------------------------------------------------------------------
7934 miller 7782 clark7839 king
7900 james7698 blake7839 king
7876 adams 7788 scott 7566 jones 7839 king
EMPNO
ENAME
MGR
SYS_CONNECT_BY_PATH(EMPNO||ENA
7369
SMITH
7902
->7369SMITH
7902
FORD
7566
->7369SMITH->7902FORD
7566
JONES
7839
->7369SMITH->7902FORD->7566JONES
7839
KING
->7369SMITH->7902FORD->7566JONES->7839KING
FROM EMP
CONNECT BY EMPNO = PRIOR MGR
START WITH ENAME NOT LIKE'KING'
ORDER BY LEVEL DESC)
WHERE ENAME LIKE 'KING'
PATH
->7369SMITH->7902FORD->7566JONES->7839KING
->300smith->7902FORD->7566JONES->7839KING
->7521WARD->7698BLAKE->7839KING
->7499ALLEN->7698BLAKE->7839KING
->7654MARTIN->7698BLAKE->7839KING
->7902FORD->7566JONES->7839KING
->7900JAMES->7698BLAKE->7839KING
->7934MILLER->7782CLARK->7839KING
->7844TURNER->7698BLAKE->7839KING
->7698BLAKE->7839KING
->7782CLARK->7839KING
->7566JONES->7839KING
