WITH A_TABLE AS
(SELECT '1' REGIST_NO, '001' EXCTV_SE_CD, '1' EXCTV_SN, 'A_TEST1' EXCTV_NM FROM DUAL
UNION ALL SELECT '1' REGIST_NO, '002' EXCTV_SE_CD, '2' EXCTV_SN, 'A_TEST2' EXCTV_NM FROM DUAL
UNION ALL SELECT '1' REGIST_NO, '003' EXCTV_SE_CD, '3' EXCTV_SN, 'A_TEST3' EXCTV_NM FROM DUAL
UNION ALL SELECT '2' REGIST_NO, '001' EXCTV_SE_CD, '1' EXCTV_SN, 'A_TEST4' EXCTV_NM FROM DUAL
UNION ALL SELECT '2' REGIST_NO, '002' EXCTV_SE_CD, '2' EXCTV_SN, 'A_TEST5' EXCTV_NM FROM DUAL
)
,
B_TABLE AS
(SELECT 'A001' REQST_NO, '002' EXCTV_SE_CD, '003' HNF_CHANGE_SE_CD, '4' CHANGE_EXCTV_SN, 'B_TEST1' CHANGE_HNF_NM FROM DUAL
UNION ALL SELECT 'A001' REQST_NO, '003' EXCTV_SE_CD, '001' HNF_CHANGE_SE_CD, '3' CHANGE_EXCTV_SN, 'B_TEST2' CHANGE_HNF_NM FROM DUAL
UNION ALL SELECT 'A002' REQST_NO, '003' EXCTV_SE_CD, '001' HNF_CHANGE_SE_CD, '3' CHANGE_EXCTV_SN, 'B_TEST3' CHANGE_HNF_NM FROM DUAL
UNION ALL SELECT 'A003' REQST_NO, '003' EXCTV_SE_CD, '001' HNF_CHANGE_SE_CD, '3' CHANGE_EXCTV_SN, 'B_TEST4' CHANGE_HNF_NM FROM DUAL
UNION ALL SELECT 'A003' REQST_NO, '003' EXCTV_SE_CD, '001' HNF_CHANGE_SE_CD, '3' CHANGE_EXCTV_SN, 'B_TEST5' CHANGE_HNF_NM FROM DUAL
)
SELECT *
FROM (SELECT * FROM A_TABLE WHERE REGIST_NO = '1')A
,(SELECT * FROM B_TABLE WHERE REQST_NO = 'A001')B
위 조건의 데이터을 이용해서 변경전 데이터와 변경후 데이터을 뽑아야되는데요.
A_TABLE의 EXCTV_SN , EXCTV_SE_CD 컬럼과 B_TABLE 테이블의 CHANGE_EXCTV_SN, EXCTV_SE_CD 컬럼이 키값입니다.
B_TABLE의 HNF_CHANGE_SE_CD컬럼은 001은 변경 003은 추가입니다.
변경 및 추가건에 대해서만 데이터을 뽑아야됩니다.
--RESULT
bfCol afCol
002(A_TEST2) 002(A_TEST2,B_TEST1)
003(A_TEST3) 003(B_TEST2)
위 결과처럼 데이터을 뽑을수 있을까요.
도움 부탁드립니다. |