꿈꾸는 개발자, DBA 커뮤니티 구루비

조인 도움 부탁드립니다.

by 생각 [SQL Query] [2019.10.02 11:30:52]

안녕하세요.

테이블 컬럼 수정이 불가한 상태에서 조인 관계 적용에 어려움이 있어 도움을 구하고자 합니다.

사용 테이블
1. CODE (공통코드)

   WITH CODE(DEPT, H_CODE, D_CODE, N_CODE, CD_01, CD_02, CD_03) AS
   ( 
   SELECT '100','BANK','10','보증금(아파트)','004','11111','90000' FROM DUAL UNION ALL
   SELECT '100','BANK','20','임대료(아파트)','004','11111','90000' FROM DUAL UNION ALL
   SELECT '200','BANK','10','보증금(아파트)','081','22222','91000' FROM DUAL UNION ALL
   SELECT '200','BANK','20','임대료(아파트)','081','33333','92000' FROM DUAL UNION ALL
   SELECT '300','BANK','10','보증금(아파트)','004','44444','93000' FROM DUAL UNION ALL
   SELECT '300','BANK','11','보증금(상가)','004','55555','94000' FROM DUAL UNION ALL
   SELECT '300','BANK','20','임대료(아파트)','004','66666','95000' FROM DUAL UNION ALL
   SELECT '300','BANK','21','임대료(상가)','004','66666','95000' FROM DUAL UNION ALL
   SELECT '300','BANK','90','보증보험료','003','77777','96000' FROM DUAL
   )

* 100번 사업장은 보증금, 임대료 모계좌(CD_02), 기관코드(CD_03) 동일 사용
* 200번 사업장은 보증금, 임대료 모계좌(CD_02), 기관코드(CD_03) 분리 사용
* 300번 사업장은
보증금 아파트, 상가 모계좌(CD_02), 기관코드(CD_03) 분리 사용
임대료 아파트, 상가 모계좌(CD_02), 기관코드(CD_03) 동일 사용
* 아파트 보증금, 임대료 코드(H_CODE 10, 20), 상가 보증금, 임대료 코드(11, 21), 보증보험료(90)

2. AHST (계좌테이블)

WITH AHST(ORG_NO, TR_IL, TR_NO, ACCT, AMT) AS
   (
   SELECT '90000','20190101','001','10010','10000' FROM DUAL UNION ALL
   SELECT '90000','20190501','002','10010','100' FROM DUAL UNION ALL
   SELECT '91000','20190501','003','10020','5000' FROM DUAL UNION ALL
   SELECT '91000','20190510','004','10200','6000' FROM DUAL UNION ALL
   SELECT '92000','20190601','005','10030','500' FROM DUAL UNION ALL
   SELECT '93000','20190901','006','10040','20000' FROM DUAL UNION ALL
   SELECT '94000','20190910','007','10050','50000' FROM DUAL UNION ALL
   SELECT '95000','20191001','008','10060','1000' FROM DUAL UNION ALL
   SELECT '95000','20191001','009','10070','2000' FROM DUAL UNION ALL
   SELECT '96000','20191001','010','10080','3000' FROM DUAL
   )
 * 개인가상계좌(ACCT), 기관코드(ORG_NO = CODE 테이블 CD_03)

3. CTR (계약테이블)

WITH CTR(DEPT, KY_NO, KY_NM, ACCT_BJK,ACCT_BH) AS
   (
   SELECT '100','101','김씨','10010','' FROM DUAL UNION ALL
   SELECT '100','102','이씨','10100','' FROM DUAL UNION ALL
   SELECT '200','201','안씨','10020','' FROM DUAL UNION ALL
   SELECT '200','202','성씨','10200','' FROM DUAL UNION ALL   
   SELECT '300','301','정씨','10040','10080' FROM DUAL UNION ALL
   SELECT '300','302','상가A','10050','' FROM DUAL UNION ALL
   SELECT '300','303','상가B','10300','' FROM DUAL    
   )
  * 보증금(ACCT_BJK), 보증보험료 계좌(ACCT_BH)

4. LEASING (입주테이블)

WITH LEASING(DEPT, KY_NO, KY_NM, ACCT_IDR) AS
(
SELECT '100','101','김씨','10010'  FROM DUAL UNION ALL
SELECT '100','102','이씨','10100'  FROM DUAL UNION ALL
SELECT '200','201','안씨','10030'  FROM DUAL UNION ALL
SELECT '200','202','성씨','10600'  FROM DUAL UNION ALL  
SELECT '300','301','정씨','10060'  FROM DUAL UNION ALL
SELECT '300','302','상가A','10070' FROM DUAL 
)

5. 진행쿼리(수정 필요)

   SELECT C0.DEPT    "사업장"
         ,T0.KY_NO   "계약번호"               
         ,T0.KY_NM   "계약자"
         ,A0.ORG_NO  "기관코드"
         ,A0.TR_IL   "입금일"
         ,A0.TR_NO   "입금코드"
         ,A0.AMT     "입금액"
         ,C0.N_CODE  "계좌종류"
         ,C0.D_CODE  "계좌코드(생략가능)"
         ,C0.CD_02   "모계좌"
         ,T0.ACCT_BJK "보증금 가상계좌"        
         ,L0.ACCT_IDR "임대료 가상계좌"
         ,T0.ACCT_BH  "보증보험 가상계좌"
     FROM CODE    C0
         ,AHST    A0           
         ,CTR     T0
         ,LEASING L0
   WHERE C0.H_CODE = 'BANK'
     AND C0.D_CODE IN ('10','11','20','21','90')        
     AND C0.CD_03   = A0.ORG_NO         
     AND A0.ACCT   = T0.ACCT_BJK(+)
     AND A0.ACCT   = L0.ACCT_IDR(+)
   --AND T0.KY_NO   = L0.KY_NO
   ORDER BY C0.DEPT,T0.KY_NO,TR_NO;
 
6. 최종 결과

"사업장"	"계약번호"	"계약자"	"기관코드"	"입금일"	"입금코드"	"입금액"	"계좌종류"	"계좌코드(생략가능)"	"모계좌"	"보증금 가상계좌"	"임대료 가상계좌"	"보증보험 가상계좌"
100	101	김씨	90000	20190101	001	10000	보증금(아파트)	10	11111	10010	10010
100	101	김씨	90000	20190501	002	100	임대료(아파트)	20	11111	10010	10010
200	201	안씨	91000	20190501	003	5000	보증금(아파트)	10	22222	10020	10030
200	201	안씨	92000	20190601	005	500	임대료(아파트)	20	33333	10020	10030
200	202	성씨	91000	20190510	004	6000	보증금(아파트)	10	22222	10200
300	301	정씨	93000	20190901	006	20000	보증금(아파트)	10	44444	10040	10060	10080
300	301	정씨	95000	20191001	008	1000	임대료(아파트)	20	66666	10040	10060	10080
300	301	정씨	96000	20191001	010	3000	보증보험료	90	77777	10040	10060	10080
300	302	상가A	94000	20190910	007	50000	보증금(상가)	11	55555	10050	10070
300	302	상가A	95000	20191001	009	2000	임대료(상가)	21	66666	10050	10070

by 꼬랑지 [2019.10.02 13:49:49]

먼저 CODE (공통코드) 테이블과 AHST (계좌테이블) 의 조인조건이 AND C0.CD_03 = A0.ORG_NO 하나밖에 없는데 이거 정확한지 확인을 먼저해야 할 것 같은데요?

원하시는 결과를 보면

1) 100사업장에서 2개의 로우가 나오길 원하나 쿼리 결과에 4개가 나오는 이유는 기관코드 90000이 CODE에 2개, AHST에 2개이므로 4개가 나올 수 밖에 없습니다.

2) 300 사업장의 경우에도 5개의 로우가 나오길 원하나 쿼리 결과는 7개로 2개가 더 나오는 이유는 기관코드 95000 이 CODE에 2개, AHST에 2개로 4개가 나오기 때문입니다.

테이블간 RELATION에 대해 다시 확인하셔야 할 듯요

by 생각 [2019.10.02 15:52:04]

네 지적하신 테이블 릴레이션이 문제가 있습니다.

다만, 현 조건에서 수정이 불가하여 무리지만 쿼리를 틀어서 처리할 수 밖에 없겠네요.

좀 더 고민해보겠습니다.

by 마농 [2019.10.02 14:56:12]

WITH code(dept, h_code, d_code, n_code, cd_01, cd_02, cd_03) AS
(
SELECT '100', 'BANK', '10', '보증금(아파트)', '004', '11111', '90000' FROM dual UNION ALL
SELECT '100', 'BANK', '20', '임대료(아파트)', '004', '11111', '90000' FROM dual UNION ALL
SELECT '200', 'BANK', '10', '보증금(아파트)', '081', '22222', '91000' FROM dual UNION ALL
SELECT '200', 'BANK', '20', '임대료(아파트)', '081', '33333', '92000' FROM dual UNION ALL
SELECT '300', 'BANK', '10', '보증금(아파트)', '004', '44444', '93000' FROM dual UNION ALL
SELECT '300', 'BANK', '11', '보증금(상가)'  , '004', '55555', '94000' FROM dual UNION ALL
SELECT '300', 'BANK', '20', '임대료(아파트)', '004', '66666', '95000' FROM dual UNION ALL
SELECT '300', 'BANK', '21', '임대료(상가)'  , '004', '66666', '95000' FROM dual UNION ALL
SELECT '300', 'BANK', '90', '보증보험료'    , '003', '77777', '96000' FROM dual
)
, ahst(org_no, tr_il, tr_no, acct, amt) AS
(
SELECT '90000', '20190101', '001', '10010', 10000 FROM dual UNION ALL
SELECT '90000', '20190501', '002', '10010',   100 FROM dual UNION ALL
SELECT '91000', '20190501', '003', '10020',  5000 FROM dual UNION ALL
SELECT '91000', '20190510', '004', '10200',  6000 FROM dual UNION ALL
SELECT '92000', '20190601', '005', '10030',   500 FROM dual UNION ALL
SELECT '93000', '20190901', '006', '10040', 20000 FROM dual UNION ALL
SELECT '94000', '20190910', '007', '10050', 50000 FROM dual UNION ALL
SELECT '95000', '20191001', '008', '10060',  1000 FROM dual UNION ALL
SELECT '95000', '20191001', '009', '10070',  2000 FROM dual UNION ALL
SELECT '96000', '20191001', '010', '10080',  3000 FROM dual
)
, ctr(dept, ky_no, ky_nm, acct_bjk, acct_bh) AS
(
SELECT '100', '101', '김씨' , '10010', ''      FROM dual UNION ALL
SELECT '100', '102', '이씨' , '10100', ''      FROM dual UNION ALL
SELECT '200', '201', '안씨' , '10020', ''      FROM dual UNION ALL
SELECT '200', '202', '성씨' , '10200', ''      FROM dual UNION ALL
SELECT '300', '301', '정씨' , '10040', '10080' FROM dual UNION ALL
SELECT '300', '302', '상가A', '10050', ''      FROM dual UNION ALL
SELECT '300', '303', '상가B', '10300', ''      FROM dual
)
, leasing(dept, ky_no, ky_nm, acct_idr) AS
(
SELECT '100', '101', '김씨' , '10010' FROM dual UNION ALL
SELECT '100', '102', '이씨' , '10100' FROM dual UNION ALL
SELECT '200', '201', '안씨' , '10030' FROM dual UNION ALL
SELECT '200', '202', '성씨' , '10600' FROM dual UNION ALL
SELECT '300', '301', '정씨' , '10060' FROM dual UNION ALL
SELECT '300', '302', '상가A', '10070' FROM dual
)
SELECT c.dept     "사업장"
     , COALESCE(t1.ky_no, l1.ky_no, t2.ky_no)    "계약번호"
     , COALESCE(t1.ky_nm, l1.ky_nm, t2.ky_nm)    "계약자"
     , a.org_no   "기관코드"
     , a.tr_il    "입금일"
     , a.tr_no    "입금코드"
     , a.amt      "입금액"
     , c.n_code   "계좌종류"
     , c.d_code   "계좌코드(생략가능)"
     , c.cd_02    "모계좌"
     , t1.acct_bjk "보증금 가상계좌"
     , l1.acct_idr "임대료 가상계좌"
     , t2.acct_bh  "보증보험 가상계좌"
  FROM (SELECT dept, h_code, d_code, n_code, cd_01, cd_02, cd_03
             , ROW_NUMBER() OVER(PARTITION BY cd_03 ORDER BY d_code) rn
          FROM code
         WHERE h_code = 'BANK'
           AND d_code IN ('10','11','20','21','90')
        ) c
 INNER JOIN
       (SELECT org_no, tr_il, tr_no, acct, amt
             , ROW_NUMBER() OVER(PARTITION BY org_no ORDER BY tr_no) rn
          FROM ahst
        ) a
    ON c.cd_03 = a.org_no
   AND c.rn = a.rn
  LEFT OUTER JOIN ctr t1
    ON a.acct = t1.acct_bjk
   AND c.d_code IN ('10', '11')
  LEFT OUTER JOIN ctr t2
    ON a.acct = t2.acct_bh
   AND c.d_code IN ('90')
  LEFT OUTER JOIN leasing l1
    ON a.acct = l1.acct_idr
   AND c.d_code IN ('20', '21')
 ORDER BY 사업장, 계약번호, 입금코드
;

by 생각 [2019.10.02 15:49:44]

결과 화면이 다소 부정확한 부분이 있었는데,

감안하여 보완해주셔서 감사합니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

Rich Text Editor, commentnote

Editor toolbars

Press ALT 0 for help

◢Elements path

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변