간단한 SQL 쿼리 질문 드립니다. 0 2 216

by 식민최 [2021.09.14 17:53:23]


아래와 같은 테이블에서

field 값이 A and C인 id만 가져오고 싶습니다. (id: 9)

도움 부탁드립니다..

| id       | field |
| 1        | A     |
| 1        | B     |
| 2        | A     |
| 2        | B     |
| 2        | C     |
| 3        | A     |
| 4        | B     |
| 5        | A     |
| 5        | B     |
| 6        | C     |
| 7        | A     |
| 7        | B     |
| 7        | C     |
| 8        | A     |
| 8        | B     |
| 8        | D     |
| 9        | A     |
| 9        | C     |
| 10       | A     |
by 마농 [2021.09.14 18:02:01]
WITH t AS
(
SELECT 1 id, 'A' field FROM dual
UNION ALL SELECT  1, 'B' FROM dual
UNION ALL SELECT  2, 'A' FROM dual
UNION ALL SELECT  2, 'B' FROM dual
UNION ALL SELECT  2, 'C' FROM dual
UNION ALL SELECT  3, 'A' FROM dual
UNION ALL SELECT  4, 'B' FROM dual
UNION ALL SELECT  5, 'A' FROM dual
UNION ALL SELECT  5, 'B' FROM dual
UNION ALL SELECT  6, 'C' FROM dual
UNION ALL SELECT  7, 'A' FROM dual
UNION ALL SELECT  7, 'B' FROM dual
UNION ALL SELECT  7, 'C' FROM dual
UNION ALL SELECT  8, 'A' FROM dual
UNION ALL SELECT  8, 'B' FROM dual
UNION ALL SELECT  8, 'D' FROM dual
UNION ALL SELECT  9, 'A' FROM dual
UNION ALL SELECT  9, 'C' FROM dual
UNION ALL SELECT 10, 'A' FROM dual
)
SELECT id
  FROM t
 GROUP BY id
HAVING COUNT(*) = 2
   AND COUNT(CASE WHEN field IN ('A', 'C') THEN 1 END) = 2
;

 


by 식민최 [2021.09.14 18:37:58]

@마농 대단하십니다..

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