1. 쿼리
   {column:width=50%}{code:SQL}
   SELECT NO,SUM(TOT*DECODE(NO-LINE,1,-1,3,-1,1) ) TOT
   ,SUM(W01*DECODE(NO-LINE,1,-1,3,-1,1)) "1 월"
   ,SUM(W02*DECODE(NO-LINE,1,-1,3,-1,1)) "2 월"
   ,SUM(W03*DECODE(NO-LINE,1,-1,3,-1,1)) "3 월"
   ,SUM(W04*DECODE(NO-LINE,1,-1,3,-1,1)) "4 월"
   ,SUM(W05*DECODE(NO-LINE,1,-1,3,-1,1)) "5 월"
   ,SUM(W06*DECODE(NO-LINE,1,-1,3,-1,1)) "6 월"
   ,SUM(W01*DECODE(NO-LINE,1,-1,3,-1,1)) "7 월"
   ,SUM(W02*DECODE(NO-LINE,1,-1,3,-1,1)) "8 월"
   ,SUM(W03*DECODE(NO-LINE,1,-1,3,-1,1)) "9 월"
   ,SUM(W04*DECODE(NO-LINE,1,-1,3,-1,1)) "10 월"
   ,SUM(W05*DECODE(NO-LINE,1,-1,3,-1,1)) "11 월"
   ,SUM(W06*DECODE(NO-LINE,1,-1,3,-1,1)) "12 월"
   FROM(SELECT LINE, SUM(AMT) TOT,
   SUM(DECODE(MM,'01',AMT)) W01, SUM(DECODE(MM,'02',AMT)) W02,
   SUM(DECODE(MM,'03',AMT)) W03, SUM(DECODE(MM,'04',AMT)) W04,
   SUM(DECODE(MM,'05',AMT)) W05, SUM(DECODE(MM,'06',AMT)) W06,
   SUM(DECODE(MM,'07',AMT)) W07, SUM(DECODE(MM,'08',AMT)) W08,
   SUM(DECODE(MM,'09',AMT)) W09, SUM(DECODE(MM,'04',AMT)) W10,
   SUM(DECODE(MM,'11',AMT)) W11, SUM(DECODE(MM,'06',AMT)) W12
   FROM(SELECT Y.NO LINE, MM, SUM(AMT*DECODE(Y.NO*LINE,6,-1,1) ) AMT
   FROM(SELECT '1' LINE, SUBSTR(S2.YYMM ,5,2) MM, SUM(AMT) AMT
   FROM SALE_SUMM2 S2
   WHERE S2.YYMM LIKE '1997%'
   GROUP BY SUBSTR(S2.YYMM,5,2)
   UNION ALL
   SELECT '2' LINE, SUBSTR(SW.YYMM ,5,2) MM, SUM(AMT) AMT
   FROM SALE_WONGA SW
   WHERE SW.YYMM LIKE '1997%'
   GROUP BY SUBSTR(SW.YYMM ,5,2)) X, COPY_T Y
   WHERE Y.NO IN (LINE, 3)
   AND Y.NO <= 14
   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(SL.ACCOUNT ,1,3),'211',4, '212',5,
   '213',6, '214',7, '215',8, '221',9, '222',10, 13) LINE,
   SUBSTR(SL.YYMM,5,2) MM, SUM(AMT) AMT
   FROM SLIP_SUMM SL
   WHERE SL.YYMM LIKE '1997%'
   AND SL.ACCOUNT BETWEEN '211' AND '229'
   GROUP BY DECODE(SUBSTR(SL.ACCOUNT,1,3),'211',4, '212',5,
   '213',6, '214',7, '215',8, '221',9, '222',10, 13),
   SUBSTR(SL.YYMM,5,2)) X, COPY_T Y
   WHERE Y.NO IN ( LINE,DECODE(LINE,13,NULL,11) ) AND Y.NO <=14
   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))
   AND Y.NO <= 14
   GROUP BY Y.NO;

{column}	

# 결과
!2-48_result.JPG!
14 rows selected.

Elapsed: 00:00:00.18

# 트레이스 결과
{column:width=50%}{code:SQL}
Execution Plan
----------------------------------------------------------
Plan hash value: 2019455546

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |     1 |   107 |    45  (25)| 00:00:01 |
|   1 |  HASH GROUP BY                 |            |     1 |   107 |    45  (25)| 00:00:01 |
|   2 |   VIEW                         |            |     1 |   107 |    44  (23)| 00:00:01 |
|   3 |    HASH GROUP BY               |            |     1 |    36 |    44  (23)| 00:00:01 |
|   4 |     NESTED LOOPS               |            |     3 |   108 |    43  (21)| 00:00:01 |
|   5 |      VIEW                      |            |     2 |    60 |    39  (24)| 00:00:01 |
|   6 |       UNION-ALL                |            |       |       |            |          |
|   7 |        HASH GROUP BY           |            |     1 |    23 |            |          |
|   8 |         CONCATENATION          |            |       |       |            |          |
|   9 |          MERGE JOIN CARTESIAN  |            |    24 |   552 |    11  (19)| 00:00:01 |
|* 10 |           TABLE ACCESS FULL    | COPY_T     |     1 |     3 |     3   (0)| 00:00:01 |
|  11 |           BUFFER SORT          |            |    24 |   480 |     8  (25)| 00:00:01 |
|  12 |            VIEW                |            |    24 |   480 |     8  (25)| 00:00:01 |
|  13 |             UNION-ALL          |            |       |       |            |          |
|  14 |              HASH GROUP BY     |            |    12 |   216 |     4  (25)| 00:00:01 |
|* 15 |               TABLE ACCESS FULL| SALE_SUMM2 |    12 |   216 |     3   (0)| 00:00:01 |
|  16 |              HASH GROUP BY     |            |    12 |   216 |     4  (25)| 00:00:01 |
|* 17 |               TABLE ACCESS FULL| SALE_WONGA |    12 |   216 |     3   (0)| 00:00:01 |
|* 18 |          HASH JOIN             |            |    10 |   230 |    12  (25)| 00:00:01 |
|* 19 |           TABLE ACCESS FULL    | COPY_T     |    14 |    42 |     3   (0)| 00:00:01 |
|  20 |           VIEW                 |            |    24 |   480 |     8  (25)| 00:00:01 |
|  21 |            UNION-ALL           |            |       |       |            |          |
|  22 |             HASH GROUP BY      |            |    12 |   216 |     4  (25)| 00:00:01 |
|* 23 |              TABLE ACCESS FULL | SALE_SUMM2 |    12 |   216 |     3   (0)| 00:00:01 |
|  24 |             HASH GROUP BY      |            |    12 |   216 |     4  (25)| 00:00:01 |
|* 25 |              TABLE ACCESS FULL | SALE_WONGA |    12 |   216 |     3   (0)| 00:00:01 |
|  26 |        HASH GROUP BY           |            |     1 |    23 |    15  (20)| 00:00:01 |
|  27 |         VIEW                   |            |     1 |    23 |    14  (15)| 00:00:01 |
|  28 |          HASH GROUP BY         |            |     1 |    27 |            |          |
|  29 |           CONCATENATION        |            |       |       |            |          |
|* 30 |            HASH JOIN           |            |     1 |    27 |     7  (15)| 00:00:01 |
|* 31 |             TABLE ACCESS FULL  | SLIP_SUMM  |     1 |    21 |     3   (0)| 00:00:01 |
|* 32 |             TABLE ACCESS FULL  | COPY_T     |    14 |    84 |     3   (0)| 00:00:01 |
|* 33 |            HASH JOIN           |            |     1 |    27 |     7  (15)| 00:00:01 |
|* 34 |             TABLE ACCESS FULL  | SLIP_SUMM  |     1 |    21 |     3   (0)| 00:00:01 |
|* 35 |             TABLE ACCESS FULL  | COPY_T     |    14 |    84 |     3   (0)| 00:00:01 |
|* 36 |      TABLE ACCESS FULL         | COPY_T     |     1 |     6 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------------------------------------------------------------
  10 - filter("Y"."NO"=3 AND "Y"."NO"<=14)
  15 - filter("S2"."YYMM" LIKE '1997%')
  17 - filter("SW"."YYMM" LIKE '1997%')
  18 - access("Y"."NO"=TO_NUMBER("LINE"))
  19 - filter("Y"."NO"<=14 AND LNNVL("Y"."NO"=3))
  23 - filter("S2"."YYMM" LIKE '1997%')
  25 - filter("SW"."YYMM" LIKE '1997%')
  30 - access("Y"."NO"=TO_NUMBER(DECODE(DECODE(SUBSTR("SL"."ACCOUNT",1,3),'211',4,'212',5,'213',6,'214',7,'215',8,'221',9,'222',10,13),13,NULL,'11')))
  31 - filter("SL"."ACCOUNT">='211' AND "SL"."ACCOUNT"<='229' AND "SL"."YYMM" LIKE '1997%')
  32 - filter("Y"."NO"<=14)
  33 - access("Y"."NO"=DECODE(SUBSTR("SL"."ACCOUNT",1,3),'211',4,'212',5,'213',6,'214
              ',7,'215',8,'221',9,'222',10,13))
       filter(LNNVL("Y"."NO"=TO_NUMBER(DECODE(DECODE(SUBSTR("SL"."ACCOUNT",1,3),'211'
              ,4,'212',5,'213',6,'214',7,'215',8,'221',9,'222',10,13),13,NULL,'11'))))
  34 - filter("SL"."ACCOUNT"<='229' AND "SL"."ACCOUNT">='211' AND "SL"."YYMM" LIKE '1997%')  35 - filter("Y"."NO"<=14)
  36 - filter("Y"."NO"<=14 AND ("Y"."NO"="LINE" OR
              "Y"."NO"=DECODE("LINE",3,12,11,12) OR "Y"."NO"=DECODE("LINE",3,14,11,14,13,14)))

{column}