-
[ORACLE] 한꺼번에 집어넣기 - GROUP BY AND MAX 이용혼자서 공부하기/SQL 2008. 4. 28. 17:08728x90반응형SMALL
SELECT
MAX(C.STR_CODE) AS STR_CODE
,MAX(C.ITM_CODE) AS ITM_CODE
,C.PAY_YMD AS PAY_YMD
,SUM(C.TEMP) AS TEMP
,SUM(C.TEMP1) AS TEMP1
,SUM(C.TEMP2) AS TEMP2
,(SUM(C.TEMP) - SUM(C.TEMP1) + SUM(C.TEMP2)) AS TEMP3
FROM
(SELECT
MAX(STR_CODE) AS STR_CODE
,MAX(ITM_CODE) AS ITM_CODE
,PAY_YMD AS PAY_YMD
,PAY_FLAG AS PAY_FLAG
,QTY AS QTY
,DECODE(PAY_FLAG, '01', QTY, 0) AS TEMP
,DECODE(PAY_FLAG, '02', QTY, 0) AS TEMP1
,DECODE(PAY_FLAG, '03', QTY, 0) AS TEMP2
FROM
CM060
GROUP BY
PAY_YMD,PAY_FLAG,QTY
ORDER BY
PAY_YMD,PAY_FLAG,QTY
) C
WHERE
C.STR_CODE =:STR_CODE
AND C.PAY_YMD BETWEEN :FROM_YMD AND :TO_YMD
GROUP BY
C.PAY_YMD
ORDER BY
C.PAY_YMD<위의 그림을 아래의 형식으로 바꾸는 쿼리>
728x90반응형LIST'혼자서 공부하기 > SQL' 카테고리의 다른 글
[ORACLE PL/SQL] CURCOR (0) 2008.05.22 [ORACLE PL/SQL] CURCOR (0) 2008.05.22 [프로시저] 만드는 법 (0) 2008.05.20 ORA-01427 (0) 2008.03.26 [ORACLE] ORA-00904 (0) 2008.03.25