오라클 WITH절 질문입니다. 0 4 522

by HyunKee [2020.08.12 15:55:27]



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

위의 결과값 처럼 홍길동, 세종대왕의 장학금을 받지 않은 년도,학기에는 빈 값으로 나오게끔 하려고합니다.

많은 답변 부탁드려요 .. !

 

 

by pajama [2020.08.12 16:11:43]

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

 


by HyunKee [2020.08.12 16:34:15]

ㅠㅠ 저생각을 못했네요...

감사합니다! 좋은하루 보내세요


by 마농 [2020.08.14 16:06:57]

테이블이 두개인 것처럼 질문하셨지만?
실제로는 하나의 테이블을 조건을 달리하여 두번 조회한것일 것 같습니다.
이렇게 두번 나누어 조회하여 다시 조인하는 방식은 좋지 않습니다.
그냥 한번에 조회하면 조인 안해도 됩니다.
 

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
;

 


by HyunKee [2020.08.21 13:51:25]

바빠서 마농님 답변 지금 보게 되었네요 ㅠㅠ..
역시 마농님 이시네요.. 말씀하신대로 실제로는 하나의 테이블입니다 ㄷㄷ 

답변 감사합니다. 쿼리 보았는데 정말 깔끔하네요.. 항상 잘 배우고 감사한 마음 입니다. 감사합니다 !

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