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

2.1 개념 및 특징
2.2 양쪽 OUTER조인의 해결
2.3 특이한 활용사례

2.1 개념 및 특징

개념

m:m 조인해결

2.2 양쪽 OUTER조인의 해결


SELECT 년월, nvl(sum(불입금액),0), nvl(sum(인출금액),0)
FROM ( SELECT 관리번호, 
              substr(불입일자,1,6)    년월,
              원화금액                  불입금액,
              to_number(null)        인출금액
        FROM  예입내역
        WHERE 불입일자  between  :in_date  and  :end_date
       UNION ALL
       SELECT 관리번호,
              substr(인출일자,1,6)   년월,
              to_number(null)       불입금액,
              원화금액                  인출금액
        FROM  인출내역
        WHERE 인출일자  between  :in_date  and   :end_date )
GROUP BY 년월 ;

2.3 특이한 활용사례

2.3.1 이종(異種)로우들의 고정양식 출력


SELECT sum(tot*decode(NO-LINE,1,-1,3,-1,1) ) tot,
       sum(W01*decode(NO-LINE,1,-1,3,-1,1)), ...........................
FROM  ( SELECT LINE, sum(AMT)  TOT,
               sum(decode(MM,'01',AMT)) W01,  ........................
        FROM ( SELECT y.NO LINE, MM, 
                      sum(AMT*decode(y.NO*LINE,6,-1,1)) AMT-----------------------②
               FROM  (SELECT  '1'  LINE,  substr(년월,5,2) MM,sum(AMT) AMT
                      FROM    매출집계
                          WHERE  년월 LIKE '1997%'
                      GROUP BY substr(년월,5,2)
                      UNION ALL
                      SELECT  '2'  LINE,  substr(년월,5,2) MM,sum(AMT) AMT
                      FROM    매출원가
                          WHERE  년월 LIKE '1997%'
                      GROUP BY substr(년월,5,2) ) x, COPY_T y
               WHERE y.NO IN (LINE, 3)---------------------①
               GROUP BY y.NO, MM---------------------------③
               UNION ALL
               SELECT y.NO LINE, MM,SUM(AMT*decode(y.NO*LINE,88,-1,1) ) AMT
               FROM  (SELECT decode(substr(계정,1,2),'21', 13,substr(계정,2,2)+1) LINE,
                             substr(일자,5,2) MM, 
                             SUM(AMT) AMT
                      FROM   전표집계
                          WHERE 일자 LIKE '1997%'
                      AND 계정 BETWEEN '203' AND '219'
                      GROUP BY decode(substr(계정,1,2),'21', 13,substr(계정,2,2)+1),
                               substr(일자,5,2) ) x, COPY_T y
               WHERE y.NO IN ( LINE,decode(LINE,13,NULL,11) )
               GROUP BY y.NO, MM )
       GROUP BY LINE ) x,  COPY_T  y
WHERE y.NO IN (LINE, decode(LINE,3,12, 11,12), decode(LINE,3,14,11,14,13,14)) 
GROUP BY y.NO  ;

2.3.2 전후(前後)간의 로우 비교


SELECT rpad(월,2)||' 월'    구분, 
       sum(당월)            당월, 
       sum(당월 - 전월)      증감액,
       sum(decode(당월,0,null,(당월-전월)*100 / 당월))   증감율
FROM ( SELECT decode(NO, 2, MM+1, MM+0)       월, 
              nvl(sum(decode(NO,1,AMT)), 0)   당월,
              nvl(sum(decode(NO,2,AMT)), 0)   전월
        FROM  ( SELECT decode(년월, '199612', '00',substr(년월,5,2))  MM,
                      sum(AMT)  AMT
               FROM    매출집계
                 WHERE 사업장 = '1공장'
               AND       년월 between  '199612' and  '199712'
               GROUP BY decode(년월, '199612', '00',substr(년월,5,2))) x,
               COPY_T  y
       WHERE  NO between  decode(MM,'00',2,1)  and  decode(MM,'12',1,2)
       GROUP BY decode( NO, 2, MM+1, MM+0 ) 
 GROUP BY  월 ;

2.3.2 추출컬럼의 특이한 가공


SELECT 품목,  
       Decode(NO,1,'검사',2,'합격','율(%)'),
       Sum(decode(주,0,cnt)),
       Decode(no,3, round(sum(분자)*100/sum(분모),3), sum(분자)+sum(분모)),
       Sum(decode(주,1,건수)),   Sum(decode(주,2,건수)),   Sum(decode(주,3,건수)),               
       Sum(decode(주,4,건수)),   Sum(decode(주,5,건수)),   Sum(decode(주,6,건수))
FROM ( SELECT 품목, NO, 주,
              Decode(NO, 3, round(sum(합격수)*100/sum(검사수),3),
              sum(합격수) + sum(검사수))        건수,
              Sum(decode(주, 0,null, 검사수))   분모,
              Sum(decode(주, 0,null, 합격수))   분자
 FROM (SELECT 품목,
              Decode(to_char(검사일,'yyyymm'), '199712', '0',
                     ceil((to_char(검사일,'dd')+to_char(trunc(검사일,'mm'),'d')-1)/7)) 주,
              Count(*)       검사수,
              0              합격수,
              1              SW
       FROM  품질검사
        WHERE 공정 = 'PRESS'
       and 검사일 between  '01-DEC-97'  and  '31-JAN-98'
       GROUP BY 품목,
                Decode(to_char(검사일,'yyyymm'), '199712', '0',
                ceil((to_char(검사일,'dd')+to_char(trunc(검사일,'mm'),'d')-1)/7))
UNION ALL
      SELECT 품목,
             Decode(to_char(검사일,'yyyymm'), '199712', '0',
                    ceil((to_char(검사일,'dd')+to_char(trunc(검사일,'mm'),'d')-1)/7)) 주,
             0                 검사수,
             Count(합격항목수)  합격수,
             2                 SW
      FROM ( SELECT 품목, 검사일,
                    Count(*)  검사항목수,
                    Sum(decode(LEAST(grade,'C'),'C',1)) 합격항목수
               FROM   검사결과
               WHERE 공정 = 'PRESS'
             and 검사일 between  '01-DEC-97'  and  '31-JAN-98'
             GROUP BY 품목, 공정, 검사일, 일련번호 )
     WHERE (검사항목수 = 합격항목수) or (합격항목수 is null and rownum = 1)
     GROUP  BY 품목,
               Decode(to_char(검사일,'yyyymm'), '199712', '0',
               ceil((to_char(검사일,'dd')+to_char(trunc(검사일,'mm'),'d')-1) / 7))
) x,  COPY_T  y
WHERE NO in (SW, 3) and  NO <= 3
             GROUP BY 품목, NO, 주 )
GROUP BY 품목, NO ;