[ORACLE SQL 문제] 여러 테이블 혼합해서 결과 표 도출하기

문제 링크: http://www.gurubee.net/lecture/2190

(내 댓글 확인하기)

[퀴즈] 스터디 가입현황을 보여주는 쿼리를 작성하는 문제입니다.

---

내 쿼리

WITH student AS
(
    SELECT '001' s_id, '기민용' s_nm FROM dual
    UNION ALL SELECT '002', '이현석' FROM dual
    UNION ALL SELECT '003', '김정식' FROM dual
    UNION ALL SELECT '004', '강정식' FROM dual
)
, course AS
(
    SELECT '001' c_id, 'Database' c_nm FROM dual
    UNION ALL SELECT '002', 'Java' FROM dual
)
, study AS
(
    SELECT '001' s_id, '001' c_id, 1 chasu FROM dual
    UNION ALL SELECT '001', '001', 3 FROM dual
    UNION ALL SELECT '001', '002', 2 FROM dual
    UNION ALL SELECT '002', '001', 1 FROM dual
    UNION ALL SELECT '002', '001', 2 FROM dual
    UNION ALL SELECT '002', '001', 3 FROM dual
    UNION ALL SELECT '003', '002', 1 FROM dual
    UNION ALL SELECT '003', '002', 2 FROM dual
    UNION ALL SELECT '004', '001', 1 FROM dual
)
SELECT   CASE WHEN A.c_id = '001' THEN a.s_id END AS "ID"
       , MAX(CASE WHEN A.c_id = '001' THEN A.s_nm END) AS "성명"
       , MAX(CASE WHEN A.C_NM = 'Database' THEN 'Database' 
                  WHEN A.C_NM = 'Java' THEN 'Java'
                  ELSE '0' END) AS 스터디
       , MAX(CASE WHEN S.CHASU = '1' THEN TO_CHAR('O') ELSE NULL END) AS "1차" 
       , MAX(CASE WHEN S.CHASU = '2' THEN TO_CHAR('O') ELSE NULL END) AS "2차" 
       , MAX(CASE WHEN S.CHASU = '3' THEN TO_CHAR('O') ELSE NULL END) AS "3차" 
       , COUNT( S.CHASU) AS "참여횟수"
FROM     (SELECT ST.S_ID, ST.S_NM, C.C_ID, C.C_NM FROM student st, course C) A
       , study S
WHERE    1 = 1
AND      A.S_ID = S.S_ID(+)
AND      A.C_ID = S.C_ID(+)
GROUP BY A.S_ID, A.C_ID
ORDER BY A.S_ID
;

 

---

깨달은 점

 

[내가 생각한 것]
CASE문 사용해서 0 표시 생각해냄. 

[내가 생각 못한 것]
1. OUTER JOIN을 사용하여 '듣지 않는 강의도 포함'을 생각했으나, OUTER JOIN을 사용할 수 있는 조건을 제대로 알지 못함.

(ORA-01417: 하나의 테이블은 하나의 다른 테이블과 포괄 조인할 수 있습니다) 에러 남

    -> 해결 방법 [2021.03.23 추가]

    -> 호출하려는 두개의 테이블(student st,  source c)을 하나로 묶은 후 

​아우터 조인해주면됨. 난 A로 묶음.

2. 두번 중복되어 나오는 칼럼을 CASE문으로 한 번만 나오게 하는 방법을 몰랐음.