CTDLEM 表字段有
C_id , operation_code
001 A
002 A
003 A
004 A
001 B
002 C
004 C求同时符合operation_code里面‘A’,‘B’值的C_id 比如就是‘001’
C_id , operation_code
001 A
002 A
003 A
004 A
001 B
002 C
004 C求同时符合operation_code里面‘A’,‘B’值的C_id 比如就是‘001’
intersect
select C_id from CTDLEM where operation_code='B'
-- 1ST WAY:
SQL> SELECT C_ID
2 FROM CTDLEM T1
3 GROUP BY C_ID
4 HAVING SUM(DECODE(OPERATION_CODE,'A',1,'B',1,0)) = 2;C_ID
----
001-- 2ND WAY:
SQL> SELECT C_ID
2 FROM CTDLEM T1
3 WHERE OPERATION_CODE IN ('A','B')
4 GROUP BY C_ID
5 HAVING COUNT(DISTINCT OPERATION_CODE) = 2;C_ID
----
001SQL>
from (
select distinct c_id, operation_code
from ctdlem
where operation_code IN ('A', 'B')
)
group by c_id
having count(1) > 1
FROM CTDLEM A, CTDLEM B
WHERE A.C_ID = B.C_ID
AND A.OPERATION_CODE = 'A'
AND B.OPERATION_CODE = 'B'