查询树中排除存在于EMP2表中EMPNO的分支,前一种sql正确的,后两种使用not exists与not in 都报错误
SELECT EMPNO,RPAD(“,LEVE*3)||ENAME EMPLOYEE,MGR
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
AND ENAME!=‘SCOTT’
START WITH ENAME =‘KING’
SELECT EMPNO,RPAD(“,LEVE*3)||ENAME EMPLOYEE,MGR
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
AND NOT EXISTS (SELECT 'A' FROM EMP2 T WHERE T.EMPNO = EMPNO)
START WITH ENAME =‘KING’
SELECT EMPNO,RPAD(“,LEVE*3)||ENAME EMPLOYEE,MGR
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
AND EMPNO NOT IN (SELECT EMPNO FROM EMP2 T)
START WITH ENAME =‘KING’
SELECT EMPNO,RPAD(“,LEVE*3)||ENAME EMPLOYEE,MGR
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
AND ENAME!=‘SCOTT’
START WITH ENAME =‘KING’
SELECT EMPNO,RPAD(“,LEVE*3)||ENAME EMPLOYEE,MGR
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
AND NOT EXISTS (SELECT 'A' FROM EMP2 T WHERE T.EMPNO = EMPNO)
START WITH ENAME =‘KING’
SELECT EMPNO,RPAD(“,LEVE*3)||ENAME EMPLOYEE,MGR
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
AND EMPNO NOT IN (SELECT EMPNO FROM EMP2 T)
START WITH ENAME =‘KING’
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
AND ENAME!='SCOTT'
START WITH ENAME ='KING' SELECT EMPNO,MGR
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
AND EMPNO NOT IN (SELECT EMPNO FROM EMP2 T)
START WITH ENAME ='KING'
这个是你要的吗?
SELECT EMPNO,MGR
FROM EMP where EMPNO NOT IN (SELECT EMPNO FROM EMP2 T)
CONNECT BY PRIOR EMPNO=MGR
START WITH ENAME ='KING'
这个sql没有问题,只是只去掉树中的单个节点(EMP2中的EMPNO),我想去掉整个分支,而不是单个节点
--not in
SELECT *
FROM (SELECT LEVEL, t.empno, t.ename, t.mgr
FROM emp t
START WITH ename = 'KING'
CONNECT BY PRIOR t.empno = t.mgr) b
WHERE b.empno NOT IN (SELECT empno
FROM emp t
START WITH empno IN (SELECT empno FROM emp2)
CONNECT BY PRIOR t.empno = t.mgr)
--not exists
SELECT *
FROM (SELECT LEVEL, t.empno, t.ename, t.mgr
FROM emp t
START WITH ename = 'KING'
CONNECT BY PRIOR t.empno = t.mgr) b
WHERE NOT EXISTS (SELECT 1
FROM (SELECT t.empno, t.ename, t.mgr
FROM emp t
START WITH empno IN (SELECT empno FROM emp2)
CONNECT BY PRIOR t.empno = t.mgr) c
WHERE b.empno = c.empno);
--not in
SELECT LEVEL, t.empno, t.ename, t.mgr
FROM emp t
START WITH ename = 'KING'
CONNECT BY PRIOR t.empno = t.mgr AND
t.empno NOT IN (SELECT a.empno FROM emp2 a);--not exists
SELECT LEVEL, t.empno, t.ename, t.mgr
FROM emp t
START WITH ename = 'KING'
CONNECT BY PRIOR t.empno = t.mgr AND
NOT EXISTS (SELECT 1 FROM emp2 a WHERE a.empno = t.empno);