특정 값들을 공통으로 포함하고 있는 그룹 구하기

테이블

조건

이러한 테이블에서 이름(S_NAME)이 Pebbles 인 학생과 Frauline 인 학생이 공통적으로 속한 동아리 이름(CA_NAME)을 찾아보자.

쿼리

WITH CA AS (
  SELECT   '농구부'   AS CA_NAME, 'Pebbles'       AS S_NAME FROM DUAL UNION ALL
  SELECT   '농구부'   AS CA_NAME, 'Butterfinger'  AS S_NAME FROM DUAL UNION ALL
  SELECT   '농구부'   AS CA_NAME, 'Frauline'      AS S_NAME FROM DUAL UNION ALL
  SELECT   '배구부'   AS CA_NAME, 'Angel'         AS S_NAME FROM DUAL UNION ALL
  SELECT   '축구부'   AS CA_NAME, 'Pebbles'       AS S_NAME FROM DUAL UNION ALL
  SELECT   '축구부'   AS CA_NAME, 'Pixie'         AS S_NAME FROM DUAL UNION ALL
  SELECT   '축구부'   AS CA_NAME, 'Bubbles'       AS S_NAME FROM DUAL UNION ALL
  SELECT   '하키부'   AS CA_NAME, 'Twiggy'        AS S_NAME FROM DUAL UNION ALL
  SELECT   '하키부'   AS CA_NAME, 'Pebbles'       AS S_NAME FROM DUAL UNION ALL
  SELECT   '하키부'   AS CA_NAME, 'Frauline'      AS S_NAME FROM DUAL 
)
SELECT CA_NAME 
FROM   CA
WHERE  S_NAME IN ('Pebbles', 'Frauline')
GROUP BY CA_NAME HAVING COUNT(S_NAME)=2;

결과

---

참고

• 사용한 툴 : PL/SQL Developer
• 키워드 : group by, having, with