↵
비주류님의 댓글을 빌려 예시를 올립니다.
WITH
t
AS
(
SELECT
1 id,
'a'
val
FROM
DUAL
UNION
ALL
SELECT
1 id,
'b'
val
FROM
DUAL
UNION
ALL
SELECT
1 id,
'c'
val
FROM
DUAL
UNION
ALL
SELECT
2 id,
'a'
val
FROM
DUAL
UNION
ALL
SELECT
2 id,
'b'
val
FROM
DUAL
)
-- 값 중복이 있으면 바꿔야 겠지만 우선 올려봅니다. WITH t AS ( SELECT 1 id, 'a' val FROM DUAL UNION ALL SELECT 1 id, 'b' val FROM DUAL UNION ALL SELECT 1 id, 'c' val FROM DUAL UNION ALL SELECT 2 id, 'a' val FROM DUAL UNION ALL SELECT 2 id, 'b' val FROM DUAL ) SELECT id FROM t GROUP BY id HAVING SUM(CASE WHEN val IN ('a','b','c') THEN 1 ELSE 0 END) = 3 --SELECT * --FROM ( -- SELECT t.*, -- SUM(CASE WHEN val IN ('a','b','c') THEN 1 ELSE 0 END) -- OVER (PARTITION BY id) cnt -- FROM t -- ) --WHERE cnt = 3