구분 | 예정 | 실시 |
항구은행 | 항구 예정1 | 항구 실시1 |
항구은행 | 항구 예정2 | 항구 실시2 |
항구은행 | 항구 실시3 | |
농촌은행 | 농촌 예정1 | 농촌 실시1 |
농촌은행 | 농촌 예정2 |
테이블은 3개이고요
A : 구분값 저장 테이블
B : 예정테이블
C : 실시 테이블
A -> B 연결고리는 A키값 인 seq -> wf_seq
A -> C 연결고리는 A키값 인 seq -> wf_seq
이 구조 일 때 쿼리로 표현이 가능한가요?
해당 구분값에 값이 없으면 그림에서 처럼 null로 보이고 싶습니다
고수님들 부탁드려요 ㅜ
WITH A AS( SELECT 1 SEQ, '항구은행' NM FROM DUAL UNION ALL SELECT 2 SEQ, '농촌은행' NM FROM DUAL ) , B AS( SELECT 1 SEQ, 1 B_SEQ, '항구예정1' NM FROM DUAL UNION ALL SELECT 1 SEQ, 2 B_SEQ, '항구예정2' FROM DUAL UNION ALL SELECT 2 SEQ, 3 B_SEQ, '농촌예정1' FROM DUAL UNION ALL SELECT 2 SEQ, 4 B_SEQ, '농촌예정2' FROM DUAL ) , C AS( SELECT 1 SEQ, 1 B_SEQ, 1 C_SEQ, '항구실시1' NM FROM DUAL UNION ALL SELECT 1 SEQ, 2 B_SEQ, 2 C_SEQ, '항구실시2' FROM DUAL UNION ALL SELECT 1 SEQ, NULL B_SEQ, 3 C_SEQ, '항구실시3' FROM DUAL UNION ALL SELECT 2 SEQ, 3 B_SEQ, 4 C_SEQ, '농촌실시1' FROM DUAL ) SELECT BB.NM "구분", NM1 "예정", NM2 "실시" FROM ( SELECT B.SEQ SEQ1, B.NM NM1, C.SEQ SEQ2, C.NM NM2 FROM B, C WHERE B.SEQ(+) = C.SEQ AND B.B_SEQ(+) = C.B_SEQ UNION SELECT B.SEQ SEQ1, B.NM NM1, C.SEQ SEQ2, C.NM NM2 FROM B, C WHERE B.SEQ = C.SEQ(+) AND B.B_SEQ = C.B_SEQ(+) ) AA , A BB WHERE NVL(AA.SEQ1,AA.SEQ2) = BB.SEQ ORDER BY BB.SEQ,NM1,NM2 ;
WITH T1 (SEQ, BANK) AS ( SELECT 1, '항구은행' FROM DUAL UNION ALL SELECT 2, '농촌은행' FROM DUAL ), T2 (WF_SEQ, PLAN) AS ( SELECT 1, '항구 예정1' FROM DUAL UNION ALL SELECT 1, '항구 예정2' FROM DUAL UNION ALL SELECT 2, '농촌 예정1' FROM DUAL UNION ALL SELECT 2, '농촌 예정2' FROM DUAL ), T3 (WF_SEQ, EXEC) AS ( SELECT 1, '항구 실시1' FROM DUAL UNION ALL SELECT 1, '항구 실시2' FROM DUAL UNION ALL SELECT 1, '항구 실시3' FROM DUAL UNION ALL SELECT 2, '농촌 실시1' FROM DUAL ), TT2 (WF_SEQ, LVL, TXT) AS ( SELECT WF_SEQ , ROW_NUMBER() OVER (PARTITION BY WF_SEQ ORDER BY WF_SEQ) LVL , PLAN FROM T2 ), TT3 (WF_SEQ, LVL, TXT) AS ( SELECT WF_SEQ , ROW_NUMBER() OVER (PARTITION BY WF_SEQ ORDER BY WF_SEQ) LVL , EXEC FROM T3 ) SELECT BANK "구분", "예정", "실시" FROM T1, ( SELECT NVL(TT2.WF_SEQ, TT3.WF_SEQ) SEQ, TT2.TXT "예정", TT3.TXT "실시" FROM TT2 FULL OUTER JOIN TT3 ON TT2.WF_SEQ = TT3.WF_SEQ AND TT2.LVL = TT3.LVL) TMP1 WHERE T1.SEQ = TMP1.SEQ;