데이터베이스 질문과 답변
IN 조건의 조건들이 모두 있을 때만 TRUE인 상태를 만들고 싶습니다.
1
2
3,116
by 짱구 [SQL Query] oracle 조건 group [2021.04.20 08:58:57]
WITH T AS (
SELECT 1 seq, '한국' AS G FROM DUAL UNION ALL
SELECT 2 seq, '중국' AS G FROM DUAL UNION ALL
SELECT 3 seq, '중국' AS G FROM DUAL UNION ALL
SELECT 4 seq, '일본' AS G FROM DUAL UNION ALL
SELECT 5 seq, '한국' AS G FROM DUAL UNION ALL
SELECT 6 seq, '한국' AS G FROM DUAL UNION ALL
SELECT 7 seq, '일본' AS G FROM DUAL UNION ALL
SELECT 8 seq, '대만' AS G FROM DUAL
)
SELECT *
FROM T
WHERE G IN ('한국', '중국')
일 때는 G에 한국, 중국이 있기때문에 TRUE
WITH T AS (
SELECT 1 seq, '한국' AS G FROM DUAL UNION ALL
SELECT 2 seq, '중국' AS G FROM DUAL UNION ALL
SELECT 3 seq, '중국' AS G FROM DUAL UNION ALL
SELECT 4 seq, '일본' AS G FROM DUAL UNION ALL
SELECT 5 seq, '한국' AS G FROM DUAL UNION ALL
SELECT 6 seq, '한국' AS G FROM DUAL UNION ALL
SELECT 7 seq, '일본' AS G FROM DUAL UNION ALL
SELECT 8 seq, '대만' AS G FROM DUAL
)
SELECT *
FROM T
WHERE G IN ('한국', '중국', '미국')
일 때는 G에 한국, 중국은 있지만 미국이 없기 때문에 FALSE 가 나오게 하고 싶습니다.
제가 생각한 방법은
WITH T AS (
SELECT 1 seq, '한국' AS G FROM DUAL UNION ALL
SELECT 2 seq, '중국' AS G FROM DUAL UNION ALL
SELECT 3 seq, '중국' AS G FROM DUAL UNION ALL
SELECT 4 seq, '일본' AS G FROM DUAL UNION ALL
SELECT 5 seq, '한국' AS G FROM DUAL UNION ALL
SELECT 6 seq, '한국' AS G FROM DUAL UNION ALL
SELECT 7 seq, '일본' AS G FROM DUAL UNION ALL
SELECT 8 seq, '대만' AS G FROM DUAL
)
SELECT *
FROM T
WHERE G IN ('한국', '중국', '미국')
AND EXISTS (SELECT 1 FROM T WHERE G = '한국')
AND EXISTS (SELECT 1 FROM T WHERE G = '중국')
AND EXISTS (SELECT 1 FROM T WHERE G = '미국')
이렇게 EXISTS문장을 써서 하나씩 넣는건데 좀더 간결한 문장이 있을까요?
by 마농
[2021.04.20 09:27:52]
SELECT DECODE(COUNT(DISTINCT g), 3, 'true', 'false') flag -- IN 조건의 개수
FROM test
WHERE g IN ('한국', '중국', '미국')
;
by 짱구
[2021.04.20 09:30:40]
감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.