조인 도움 부탁드립니다. 0 4 1,011

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() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입