농부지기

☞   모든 테이블에서 PLAN_ID = 9 인 값 찾기

SELECT table_name
     , TO_NUMBER ( dbms_xmlgen.getxmltype( 'SELECT COUNT(*) c FROM ' || table_name || ' WHERE PLAN_ID = ' || '9' ).Extract('//text()') ) cnt
  FROM user_tables
 --WHERE table_name in ( 특정스키마 )
  ;
 

☞  요청사항 : 매장, 년월, 매출액이 존재 하는 테이블 있다.
               2014년도 매장별, 월별, 매출액을 조회하라.
               이때 2014년도 상반기 자료를 조회 하는데, 각 매장별 매출액이 없는 월도 0 으로 조회 되어야 된다.
               즉, 모든 매장에 2014.01 ~ 2014.06 월까지 모두 조회 되어야 한다.   

첫 번째 방법 : 아래 처럼 단순 JOIN으로 할 경우 모든월이 조회 되지 않는다.

WITH SALE (SHOP, YM, AMT) AS
   ( SELECT '동대문', '201401',  12800 FROM DUAL UNION ALL
     SELECT '동대문', '201403',  22700 FROM DUAL UNION ALL
     SELECT '동대문', '201403',  22700 FROM DUAL UNION ALL
     SELECT '동대문', '201404',  32350 FROM DUAL UNION ALL
     SELECT '동대문', '201405',  22000 FROM DUAL UNION ALL
     SELECT 'GS마켓', '201401',  13020 FROM DUAL UNION ALL
     SELECT 'GS마켓', '201404',  14100 FROM DUAL
   )
,  YM AS
   ( SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201401', 'YYYYMM'), LEVEL - 1), 'YYYYMM') AS YM
       FROM DUAL
      CONNECT BY LEVEL <= 06
   )
SELECT A.SHOP, B.YM, SUM(A.AMT) AS MM_AMT
  FROM YM B JOIN SALE A
         ON (A.YM = B.YM)
 GROUP BY A.SHOP, B.YM ;
 
-

두 번째 방법 : 모든매장, 모든월이 존재 하는 테이블 만들어서 OUTER JOIN으로 한다.
               이때 불필요하게 매장목록만 존재하는 테이블이 별도로 존재해야 된다..

WITH SALE (SHOP, YM, AMT) AS
   ( SELECT '동대문', '201401',  12800 FROM DUAL UNION ALL
     SELECT '동대문', '201403',  22700 FROM DUAL UNION ALL
     SELECT '동대문', '201403',  22700 FROM DUAL UNION ALL
     SELECT '동대문', '201404',  32350 FROM DUAL UNION ALL
     SELECT '동대문', '201405',  22000 FROM DUAL UNION ALL
     SELECT 'GS마켓', '201401',  13020 FROM DUAL UNION ALL
     SELECT 'GS마켓', '201404',  14100 FROM DUAL
   )
,  SHOP_LIST(SHOP) AS
   ( SELECT '동대문' FROM DUAL UNION ALL
     SELECT 'GS마켓' FROM DUAL
   )
,  YM AS
   ( SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201401', 'YYYYMM'), LEVEL - 1), 'YYYYMM') AS YM
       FROM DUAL
      CONNECT BY LEVEL <= 06
   )
SELECT X.SHOP, Y.YM, SUM(A.AMT) AS MM_AMT
  FROM SHOP_LIST X JOIN YM Y
              ON (1=1)
                   LEFT OUTER JOIN SALE A
              ON (    A.SHOP = X.SHOP
                  AND A.YM   = Y.YM )
 GROUP BY X.SHOP, Y.YM
 ORDER BY X.SHOP, Y.YM ;
 

☞   

세 번째 방법 : PARTITION BY OUTER JOIN을 사용하여 처리한다.

- 중요 : 이때 LEFT OUTER JOIN 이라는 문구와  PARTITON BY 절의 위치가 변경되면 안된다.

WITH SALE (SHOP, YM, AMT) AS
   ( SELECT '동대문', '201401',  12800 FROM DUAL UNION ALL
     SELECT '동대문', '201403',  22700 FROM DUAL UNION ALL
     SELECT '동대문', '201403',  22700 FROM DUAL UNION ALL
     SELECT '동대문', '201404',  32350 FROM DUAL UNION ALL
     SELECT '동대문', '201405',  22000 FROM DUAL UNION ALL
     SELECT 'GS마켓', '201401',  13020 FROM DUAL UNION ALL
     SELECT 'GS마켓', '201404',  14100 FROM DUAL
   )
,  YM AS
   ( SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201401', 'YYYYMM'), LEVEL - 1), 'YYYYMM') AS YM
       FROM DUAL
      CONNECT BY LEVEL <= 06
   )
SELECT A.SHOP, B.YM, SUM(A.AMT) AS MM_AMT
  FROM YM B LEFT OUTER JOIN SALE A PARTITION BY (A.SHOP)
         ON (A.YM = B.YM)
 GROUP BY A.SHOP, B.YM
 ORDER BY A.SHOP, B.YM;