WITH A AS ( SELECT '2019' AS YEAR, '2' AS HAKGI, '홍길동' AS NAME, '1231' AS HAKBEON, '성적우수A' AS JANGHAK FROM DUAL UNION ALL SELECT '2019' AS YEAR, '2' AS HAKGI, '임꺽정' AS NAME, '1232' AS HAKBEON, '성적우수A' AS JANGHAK FROM DUAL UNION ALL SELECT '2019' AS YEAR, '2' AS HAKGI, '이순신' AS NAME, '1233' AS HAKBEON, '성적우수A' AS JANGHAK FROM DUAL ), B AS ( SELECT '2020' AS YEAR, '1' AS HAKGI, '임꺽정' AS NAME, '1232' AS HAKBEON, '성적우수A' AS JANGHAK FROM DUAL UNION ALL SELECT '2020' AS YEAR, '1' AS HAKGI, '이순신' AS NAME, '1233' AS HAKBEON, '성적우수B' AS JANGHAK FROM DUAL UNION ALL SELECT '2020' AS YEAR, '1' AS HAKGI, '세종대왕' AS NAME, '1236' AS HAKBEON, '성적우수C' AS JANGHAK FROM DUAL ) SELECT * FROM A,B WHERE A.HAKBEON = B.HAKBEON ORDER BY A.HAKBEON;
안녕하세요. 도움 요청하고자 질문 올려봅니다 ..
원하는 결과 값 :
YEAR HAKGI NAME HAKBEON JANGHAK YEAR HAKGI NAME HAKBEON JANGHAK
2019 2 홍길동 1231 성적우수A
2019 2 임꺽정 1232 성적우수A 2020 1 임꺽정 1232 성적우수A
2019 2 이순신 1233 성적우수A 2020 1 이순신 1233 성적우수B
2020 1 세종대왕 1236 성적우수C
위의 결과값 처럼 홍길동, 세종대왕의 장학금을 받지 않은 년도,학기에는 빈 값으로 나오게끔 하려고합니다.
많은 답변 부탁드려요 .. !
FULL OUTER JOIN으로 하시면 되네요~
WITH A AS ( SELECT '2019' AS YEAR, '2' AS HAKGI, '홍길동' AS NAME, '1231' AS HAKBEON, '성적우수A' AS JANGHAK FROM DUAL UNION ALL SELECT '2019' AS YEAR, '2' AS HAKGI, '임꺽정' AS NAME, '1232' AS HAKBEON, '성적우수A' AS JANGHAK FROM DUAL UNION ALL SELECT '2019' AS YEAR, '2' AS HAKGI, '이순신' AS NAME, '1233' AS HAKBEON, '성적우수A' AS JANGHAK FROM DUAL ), B AS ( SELECT '2020' AS YEAR, '1' AS HAKGI, '임꺽정' AS NAME, '1232' AS HAKBEON, '성적우수A' AS JANGHAK FROM DUAL UNION ALL SELECT '2020' AS YEAR, '1' AS HAKGI, '이순신' AS NAME, '1233' AS HAKBEON, '성적우수B' AS JANGHAK FROM DUAL UNION ALL SELECT '2020' AS YEAR, '1' AS HAKGI, '세종대왕' AS NAME, '1236' AS HAKBEON, '성적우수C' AS JANGHAK FROM DUAL ) SELECT * FROM A FULL OUTER JOIN B ON A.HAKBEON = B.HAKBEON ORDER BY A.HAKBEON; YEAR H NAME HAKB JANGHAK YEAR H NAME HAKB JANGHAK ---- - --------- ---- ------------- ---- - ------------ ---- ------------- 2019 2 홍길동 1231 성적우수A 2019 2 임꺽정 1232 성적우수A 2020 1 임꺽정 1232 성적우수A 2019 2 이순신 1233 성적우수A 2020 1 이순신 1233 성적우수B 2020 1 세종대왕 1236 성적우수C
테이블이 두개인 것처럼 질문하셨지만?
실제로는 하나의 테이블을 조건을 달리하여 두번 조회한것일 것 같습니다.
이렇게 두번 나누어 조회하여 다시 조인하는 방식은 좋지 않습니다.
그냥 한번에 조회하면 조인 안해도 됩니다.
WITH t AS ( SELECT '2019' year, '2' hakgi, '홍길동' name, '1231' hakbeon, '성적우수a' janghak FROM dual UNION ALL SELECT '2019', '2', '임꺽정' , '1232', '성적우수A' FROM dual UNION ALL SELECT '2019', '2', '이순신' , '1233', '성적우수A' FROM dual UNION ALL SELECT '2020', '1', '임꺽정' , '1232', '성적우수A' FROM dual UNION ALL SELECT '2020', '1', '이순신' , '1233', '성적우수B' FROM dual UNION ALL SELECT '2020', '1', '세종대왕', '1236', '성적우수C' FROM dual ) SELECT hakbeon , name , MIN(CASE WHEN (year, hakgi) IN (('2019', '2')) THEN janghak END) janghak_2019_2 , MIN(CASE WHEN (year, hakgi) IN (('2020', '1')) THEN janghak END) janghak_2020_1 FROM t WHERE (year, hakgi) IN (('2019', '2'), ('2020', '1')) GROUP BY hakbeon, name ORDER BY hakbeon ;