--使用start with ...connect by prior 从根节点开始遍历 SQL> select empno,mgr,ename,job from emp 2 start with empno = 7839 --在这里设定起始处 3 connect by prior empno = mgr; EMPNO MGR ENAME JOB ---------- ---------- ---------- --------- 7839 KING PRESIDENT 7566 7839 JONES MANAGER 7788 7566 SCOTT ANALYST 7876 7788 ADAMS CLERK更多参考: SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
--使用start with ...connect by prior 从根节点开始遍历
SQL> select empno,mgr,ename,job from emp
2 start with empno = 7839 --在这里设定起始处
3 connect by prior empno = mgr; EMPNO MGR ENAME JOB
---------- ---------- ---------- ---------
7839 KING PRESIDENT
7566 7839 JONES MANAGER
7788 7566 SCOTT ANALYST
7876 7788 ADAMS CLERK更多参考:
SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
然后取level为1的内容就是了
用connect_by_isleaf效率不高
2 where connect_by_isleaf=1
3 start with empno = 7839
4 connect by prior empno = mgr;
EMPNO MGR ENAME JOB
--------------------- ----- ---------- ---------
7876 7788 ADAMS CLERK
7369 7902 SMITH CLERK
7499 7698 ALLEN SALESMAN
7521 7698 WARD SALESMAN
7654 7698 MARTIN SALESMAN
7844 7698 TURNER SALESMAN
7900 7698 JAMES CLERK
7934 7782 MILLER CLERK
8 rows selected
SQL>
SQL> select empno,mgr,ename,job from emp a
2 where not exists(select 1 from emp b where a.empno=b.mgr);
EMPNO MGR ENAME JOB
--------------------- ----- ---------- ---------
7369 7902 SMITH CLERK
7499 7698 ALLEN SALESMAN
7521 7698 WARD SALESMAN
7654 7698 MARTIN SALESMAN
7844 7698 TURNER SALESMAN
7876 7788 ADAMS CLERK
7900 7698 JAMES CLERK
7934 7782 MILLER CLERK
8 rows selected
SQL>
如果限定了根节点的话只能用connect_by_isleaf