데이터베이스 질문과 답변

IN 조건의 조건들이 모두 있을 때만 TRUE인 상태를 만들고 싶습니다. 1 2 3,470

by 짱구 [SQL Query] oracle 조건 group [2021.04.20 08:58:57]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
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 가 나오게 하고 싶습니다.

제가 생각한 방법은

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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]
?
1
2
3
4
SELECT DECODE(COUNT(DISTINCT g), 3, 'true', 'false') flag -- IN 조건의 개수
  FROM test
 WHERE g IN ('한국', '중국', '미국')
;

 

by 짱구 [2021.04.20 09:30:40]

감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입