데이터베이스 질문과 답변
/* newweb.xml : m.newweb.SelectDailySalesList */
SELECT * FROM (
--SELECT B.SALE_QTY, B.SALE_AMT, B.ORDER_QTY, B.ORDER_AMT, MEDIA_CODE, DAY_DD, DAY_CNT FROM
SELECT SUM(A.ORDER_QTY - A.CANCEL_QTY - A.CLAIM_QTY + A.CLAIM_CAN_QTY) AS SALE_QTY /* 주문값 합계 */
,SUM(ORDER_AMT - CANCEL_AMT - (CLAIM_AMT - CLAIM_CAN_AMT)) AS SALE_AMT
,SUM(A.ORDER_QTY) AS ORDER_QTY
,SUM(A.ORDER_AMT) AS ORDER_AMT
,(SUM(A.ORDER_OUT_AMT) - SUM(A.RETURN_AMT)) AS SALE_AMT_2 /* 매출값 합계 */
,(SUM(A.ORDER_OUT_QTY) - SUM(A.RETURN_QTY)) AS SALE_QTY_2
,SUM(A.ORDER_OUT_AMT) AS ORDER_OUT_AMT_2
,SUM(A.ORDER_OUT_QTY) AS ORDER_OUT_QTY_2
,MAX(TO_CHAR(TO_DATE(A.DAY_DD), 'mm-dd (dy)', 'NLS_DATE_LANGUAGE=korean')) AS DAY_DY
,A.DAY_DD AS DAY_DD
,A.MEDIA_CODE_NAME AS MEDIA_CODE
,COUNT(A.DAY_DD) OVER(PARTITION BY A.DAY_DD) AS DAY_CNT
,GROUPING(MEDIA_CODE_NAME) AS RESULT
FROM (
SELECT SUM(A.ORDER_QTY) AS ORDER_QTY, /* (amt_flag)default: 부가세 제외(2) */
SUM(DECODE( '2' , '1', ORDER_AMT, '2', ORDER_NET, '3'/*, ORDER_FEE*/)) AS ORDER_AMT,
SUM(A.ORDER_OUT_QTY) AS ORDER_OUT_QTY,
SUM(DECODE( '2' , '1', ORDER_OUT_AMT, '2', ORDER_OUT_NET, '3'/*, ORDER_FEE*/)) AS ORDER_OUT_AMT,
SUM(A.CANCEL_QTY) AS CANCEL_QTY,
SUM(DECODE( '2' , '1', CANCEL_AMT, '2', CANCEL_NET, '3'/*, CANCEL_FEE*/)) AS CANCEL_AMT,
SUM(A.CLAIM_QTY) AS CLAIM_QTY,
SUM(A.CLAIM_CAN_QTY) AS CLAIM_CAN_QTY,
SUM(DECODE( '2' , '1', CLAIM_AMT, '2', CLAIM_NET, '3'/*, CLAIM_FEE*/)) AS CLAIM_AMT,
SUM(DECODE( '2' , '1', CLAIM_CAN_AMT, '2', CLAIM_CAN_NET, '3'/*, CLAIM_CAN_FEE*/)) AS CLAIM_CAN_AMT,
SUM(A.RETURN_QTY) AS RETURN_QTY,
SUM(DECODE( '2' , '1', RETURN_AMT, '2', RETURN_NET, '3'/*, RETURN_FEE*/)) AS RETURN_AMT,
TO_CHAR(A.GATHER_DATE, 'yyyy-mm-dd') AS DAY_DD ,
A.MEDIA_CODE AS MEDIA_CODE,
FUN_GET_MEDIA_NAME(A.MEDIA_CODE) AS MEDIA_CODE_NAME
FROM (
SELECT S.GATHER_DATE,
S.ORDER_MEDIA AS ORDER_MEDIA,
S.MEDIA_GB AS MEDIA_GB,
S.MEDIA_CODE AS MEDIA_CODE,
S.LGROUP,
S.ORDER_QTY AS ORDER_QTY,
S.ORDER_AMT AS ORDER_AMT,
S.ORDER_NET AS ORDER_NET,
S.ORDER_OUT_QTY AS ORDER_OUT_QTY,
S.ORDER_OUT_AMT AS ORDER_OUT_AMT,
S.ORDER_OUT_NET AS ORDER_OUT_NET,
S.CANCEL_QTY AS CANCEL_QTY,
S.CANCEL_AMT AS CANCEL_AMT,
S.CANCEL_NET AS CANCEL_NET,
S.CLAIM_QTY AS CLAIM_QTY,
S.CLAIM_CAN_QTY AS CLAIM_CAN_QTY,
S.CLAIM_AMT AS CLAIM_AMT,
S.CLAIM_NET AS CLAIM_NET,
S.CLAIM_CAN_AMT AS CLAIM_CAN_AMT,
S.CLAIM_CAN_NET AS CLAIM_CAN_NET,
S.RETURN_QTY AS RETURN_QTY,
S.RETURN_AMT AS RETURN_AMT,
S.RETURN_NET AS RETURN_NET
FROM TSDLGROUP S
WHERE GATHER_DATE < TRUNC(SYSDATE)
UNION ALL
SELECT S.GATHER_DATE AS GATHER_DATE,
S.ORDER_MEDIA AS ORDER_MEDIA,
S.MEDIA_GB AS MEDIA_GB,
S.MEDIA_CODE AS MEDIA_CODE,
S.LGROUP AS LGROUP,
SUM(S.ORDER_QTY) AS ORDER_QTY,
SUM(S.ORDER_AMT ) AS ORDER_AMT,
SUM(S.ORDER_NET ) AS ORDER_NET,
0 AS ORDER_OUT_QTY,
0 AS ORDER_OUT_AMT,
0 AS ORDER_OUT_NET,
SUM(S.CANCEL_QTY) AS CANCEL_QTY,
SUM(S.CANCEL_AMT) AS CANCEL_AMT,
SUM(S.CANCEL_NET) AS CANCEL_NET,
SUM(S.CLAIM_QTY) AS CLAIM_QTY,
SUM(S.CLAIM_CAN_QTY) AS CLAIM_CAN_QTY,
SUM(S.CLAIM_AMT) AS CLAIM_AMT,
SUM(S.CLAIM_NET) AS CLAIM_NET,
SUM(S.CLAIM_CAN_AMT) AS CLAIM_CAN_AMT,
SUM(S.CLAIM_CAN_NET) AS CLAIM_CAN_NET,
0 AS RETURN_QTY,
0 AS RETURN_AMT,
0 AS RETURN_NET
FROM (SELECT TRUNC(O.ORDER_DATE) AS GATHER_DATE,
M.ORDER_MEDIA,
A.MEDIA_GB,
O.MEDIA_CODE,
G.LGROUP,
DECODE(ORDER_D_SEQ, '001', ORDER_QTY, 0) AS ORDER_QTY,
RSALE_AMT AS ORDER_AMT,
RSALE_NET AS ORDER_NET,
0 AS CANCEL_QTY,
0 AS CANCEL_AMT,
0 AS CANCEL_NET,
0 AS CLAIM_QTY,
0 AS CLAIM_AMT,
0 AS CLAIM_NET,
0 AS CLAIM_CAN_QTY,
0 AS CLAIM_CAN_AMT,
0 AS CLAIM_CAN_NET
FROM TORDERDT O, TORDERM M, TMEDIA A, TGOODS G
WHERE O.ORDER_DATE > = TRUNC(SYSDATE)
AND O.ORDER_DATE < = SYSDATE
AND O.GOODS_CODE = G.GOODS_CODE
AND O.ORDER_NO = M.ORDER_NO
AND O.MEDIA_CODE = A.MEDIA_CODE
AND G.GIFT_YN = '0'
UNION ALL
SELECT TRUNC(D.CANCEL_DATE) AS GATHER_DATE,
M.ORDER_MEDIA,
A.MEDIA_GB,
D.MEDIA_CODE,
G.LGROUP,
0 AS ORDER_QTY,
0 AS ORDER_AMT,
0 AS ORDER_NET,
DECODE(ORDER_D_SEQ, '001', CANCEL_QTY, 0) AS CANCEL_QTY,
RSALE_AMT AS CANCEL_AMT,
RSALE_NET AS CANCEL_NET,
0 AS CLAIM_QTY,
0 AS CLAIM_AMT,
0 AS CLAIM_NET,
0 AS CLAIM_CAN_QTY,
0 AS CLAIM_CAN_AMT,
0 AS CLAIM_CAN_NET
FROM TCANCELDT D, TGOODS G, TORDERM M, TMEDIA A
WHERE D.CANCEL_DATE > = TRUNC(SYSDATE)
AND D.CANCEL_DATE < = SYSDATE
AND D.GOODS_CODE = G.GOODS_CODE
AND D.ORDER_NO = M.ORDER_NO
AND D.MEDIA_CODE = A.MEDIA_CODE
AND G.GIFT_YN = '0'
UNION ALL
SELECT TRUNC(D.CLAIM_DATE) AS GATHER_DATE,
M.ORDER_MEDIA,
A.MEDIA_GB,
D.MEDIA_CODE,
G.LGROUP,
0 AS ORDER_QTY,
0 AS ORDER_AMT,
0 AS ORDER_NET,
0 AS CANCEL_QTY,
0 AS CANCEL_AMT,
0 AS CANCEL_NET,
DECODE(D.CLAIM_GB, '30', DECODE(D.ORDER_D_SEQ, '001', D.CLAIM_QTY, 0), 0) AS CLAIM_QTY,
DECODE(D.CLAIM_GB, '30', D.RSALE_AMT, 0) AS CLAIM_AMT,
DECODE(D.CLAIM_GB, '30', D.RSALE_NET, 0) AS CLAIM_NET,
DECODE(D.CLAIM_GB, '31', DECODE(D.ORDER_D_SEQ, '001', D.CLAIM_QTY, 0), 0) AS CLAIM_CAN_QTY,
DECODE(D.CLAIM_GB, '31', D.RSALE_AMT, 0) AS CLAIM_CAN_AMT,
DECODE(D.CLAIM_GB, '31', D.RSALE_NET, 0) AS CLAIM_CAN_NET
FROM TCLAIMDT D, TORDERM M, TMEDIA A, TGOODS G
WHERE D.CLAIM_DATE > = TRUNC(SYSDATE)
AND D.CLAIM_DATE < = SYSDATE
AND D.GOODS_CODE = G.GOODS_CODE
AND D.ORDER_NO = M.ORDER_NO
AND D.MEDIA_CODE = A.MEDIA_CODE
AND G.GIFT_YN = '0'
) S
GROUP BY S.GATHER_DATE ,
S.ORDER_MEDIA ,
S.MEDIA_GB ,
S.MEDIA_CODE ,
S.LGROUP
) A
WHERE A.GATHER_DATE > = TO_DATE( '2021-04-17' /**P*/, 'yyyy-mm-dd')
AND A.GATHER_DATE < TO_DATE( '2021-04-23' /**P*/, 'yyyy-mm-dd') + INTERVAL '1' DAY
AND A.MEDIA_CODE NOT IN (SELECT T.REMARK1 FROM TCODE T WHERE T.CODE_LGROUP = 'O501')
GROUP BY TO_CHAR(A.GATHER_DATE, 'yyyy-mm-dd'), A.MEDIA_CODE
UNION ALL
SELECT SUM(A.ORDER_QTY) AS ORDER_QTY,
SUM(DECODE( '2' , '1', ORDER_AMT, '2', ORDER_NET, '3'/*, ORDER_FEE*/)) AS ORDER_AMT,
SUM(A.ORDER_OUT_QTY) AS ORDER_OUT_QTY,
SUM(DECODE( '2' , '1', ORDER_OUT_AMT, '2', ORDER_OUT_NET, '3'/*, ORDER_FEE*/)) AS ORDER_OUT_AMT,
SUM(A.CANCEL_QTY) AS CANCEL_QTY,
SUM(DECODE( '2' , '1', CANCEL_AMT, '2', CANCEL_NET, '3'/*, CANCEL_FEE*/)) AS CANCEL_AMT,
SUM(A.CLAIM_QTY) AS CLAIM_QTY,
SUM(A.CLAIM_CAN_QTY) AS CLAIM_CAN_QTY,
SUM(DECODE( '2' , '1', CLAIM_AMT, '2', CLAIM_NET, '3'/*, CLAIM_FEE*/)) AS CLAIM_AMT,
SUM(DECODE( '2' , '1', CLAIM_CAN_AMT, '2', CLAIM_CAN_NET, '3'/*, CLAIM_CAN_FEE*/)) AS CLAIM_CAN_AMT,
SUM(A.RETURN_QTY) AS RETURN_QTY,
SUM(DECODE( '2' , '1', RETURN_AMT, '2', RETURN_NET, '3'/*, RETURN_FEE*/)) AS RETURN_AMT,
TO_CHAR(A.GATHER_DATE, 'yyyy-mm-dd') AS DAY_DD ,
A.MEDIA_CODE AS MEDIA_CODE,
FUN_GET_MEDIA_NAME(A.MEDIA_CODE) AS MEDIA_CODE_NAME
FROM (
SELECT S.GATHER_DATE,
S.ORDER_MEDIA AS ORDER_MEDIA,
S.MEDIA_GB AS MEDIA_GB,
S.MEDIA_CODE AS MEDIA_CODE,
S.LGROUP,
S.ORDER_QTY AS ORDER_QTY,
S.ORDER_AMT AS ORDER_AMT,
S.ORDER_NET AS ORDER_NET,
S.ORDER_OUT_QTY AS ORDER_OUT_QTY,
S.ORDER_OUT_AMT AS ORDER_OUT_AMT,
S.ORDER_OUT_NET AS ORDER_OUT_NET,
S.CANCEL_QTY AS CANCEL_QTY,
S.CANCEL_AMT AS CANCEL_AMT,
S.CANCEL_NET AS CANCEL_NET,
S.CLAIM_QTY AS CLAIM_QTY,
S.CLAIM_CAN_QTY AS CLAIM_CAN_QTY,
S.CLAIM_AMT AS CLAIM_AMT,
S.CLAIM_NET AS CLAIM_NET,
S.CLAIM_CAN_AMT AS CLAIM_CAN_AMT,
S.CLAIM_CAN_NET AS CLAIM_CAN_NET,
S.RETURN_QTY AS RETURN_QTY,
S.RETURN_AMT AS RETURN_AMT,
S.RETURN_NET AS RETURN_NET
FROM TSDPALGROUP S
WHERE GATHER_DATE < TRUNC(SYSDATE)
UNION ALL
SELECT S.GATHER_DATE AS GATHER_DATE,
S.ORDER_MEDIA AS ORDER_MEDIA,
S.MEDIA_GB AS MEDIA_GB,
S.MEDIA_CODE AS MEDIA_CODE,
S.LGROUP AS LGROUP,
SUM(S.ORDER_QTY) AS ORDER_QTY,
SUM(S.ORDER_AMT ) AS ORDER_AMT,
SUM(S.ORDER_NET ) AS ORDER_NET,
0 AS ORDER_OUT_QTY,
0 AS ORDER_OUT_AMT,
0 AS ORDER_OUT_NET,
SUM(S.CANCEL_QTY) AS CANCEL_QTY,
SUM(S.CANCEL_AMT) AS CANCEL_AMT,
SUM(S.CANCEL_NET) AS CANCEL_NET,
SUM(S.CLAIM_QTY) AS CLAIM_QTY,
SUM(S.CLAIM_CAN_QTY) AS CLAIM_CAN_QTY,
SUM(S.CLAIM_AMT) AS CLAIM_AMT,
SUM(S.CLAIM_NET) AS CLAIM_NET,
SUM(S.CLAIM_CAN_AMT) AS CLAIM_CAN_AMT,
SUM(S.CLAIM_CAN_NET) AS CLAIM_CAN_NET,
0 AS RETURN_QTY,
0 AS RETURN_AMT,
0 AS RETURN_NET
FROM (SELECT TRUNC(O.ORDER_DATE) AS GATHER_DATE,
M.ORDER_MEDIA,
A.MEDIA_GB,
O.MEDIA_CODE,
G.LGROUP,
DECODE(ORDER_D_SEQ, '001', ORDER_QTY, 0) AS ORDER_QTY,
O.RSALE_AMT + ROUND(((O.DC_AMT - O.REMARK3_N)/O.ORDER_QTY)*O.ORDER_QTY) AS ORDER_AMT,
O.RSALE_NET + DECODE(O.RSALE_VAT,0,ROUND(((O.DC_AMT - O.REMARK3_N)/O.ORDER_QTY)*O.ORDER_QTY), ROUND(((O.DC_AMT - O.REMARK3_N)/O.ORDER_QTY)*O.ORDER_QTY/1.1,0)) AS ORDER_NET,
0 AS CANCEL_QTY,
0 AS CANCEL_AMT,
0 AS CANCEL_NET,
0 AS CLAIM_QTY,
0 AS CLAIM_AMT,
0 AS CLAIM_NET,
0 AS CLAIM_CAN_QTY,
0 AS CLAIM_CAN_AMT,
0 AS CLAIM_CAN_NET
FROM TORDERDT O, TORDERM M, TMEDIA A, TGOODS G
WHERE O.ORDER_DATE > = TRUNC(SYSDATE)
AND O.ORDER_DATE < = SYSDATE
AND O.GOODS_CODE = G.GOODS_CODE
AND O.ORDER_NO = M.ORDER_NO
AND O.MEDIA_CODE = A.MEDIA_CODE
AND G.GIFT_YN = '0'
AND O.MEDIA_CODE IN (SELECT REMARK1 FROM TCODE WHERE CODE_LGROUP = 'O501')
UNION ALL
SELECT TRUNC(D.CANCEL_DATE) AS GATHER_DATE,
M.ORDER_MEDIA,
A.MEDIA_GB,
D.MEDIA_CODE,
G.LGROUP,
0 AS ORDER_QTY,
0 AS ORDER_AMT,
0 AS ORDER_NET,
DECODE(ORDER_D_SEQ, '001', CANCEL_QTY, 0) AS CANCEL_QTY,
D.RSALE_AMT + ROUND(((D.DC_AMT - D.REMARK3_N)/D.CANCEL_QTY)*D.CANCEL_QTY) AS CANCEL_AMT,
D.RSALE_NET + DECODE(D.RSALE_VAT,0,ROUND(((D.DC_AMT - D.REMARK3_N)/D.CANCEL_QTY)*D.CANCEL_QTY), ROUND(((D.DC_AMT - D.REMARK3_N)/D.CANCEL_QTY)*D.CANCEL_QTY/1.1,0)) AS CANCEL_NET,
0 AS CLAIM_QTY,
0 AS CLAIM_AMT,
0 AS CLAIM_NET,
0 AS CLAIM_CAN_QTY,
0 AS CLAIM_CAN_AMT,
0 AS CLAIM_CAN_NET
FROM TCANCELDT D, TGOODS G, TORDERM M, TMEDIA A
WHERE D.CANCEL_DATE > = TRUNC(SYSDATE)
AND D.CANCEL_DATE < = SYSDATE
AND D.GOODS_CODE = G.GOODS_CODE
AND D.ORDER_NO = M.ORDER_NO
AND D.MEDIA_CODE = A.MEDIA_CODE
AND G.GIFT_YN = '0'
AND D.MEDIA_CODE IN (SELECT REMARK1 FROM TCODE WHERE CODE_LGROUP = 'O501')
UNION ALL
SELECT TRUNC(D.CLAIM_DATE) AS GATHER_DATE,
M.ORDER_MEDIA,
A.MEDIA_GB,
D.MEDIA_CODE,
G.LGROUP,
0 AS ORDER_QTY,
0 AS ORDER_AMT,
0 AS ORDER_NET,
0 AS CANCEL_QTY,
0 AS CANCEL_AMT,
0 AS CANCEL_NET,
DECODE(D.CLAIM_GB, '30', DECODE(D.ORDER_D_SEQ, '001', D.CLAIM_QTY, 0), 0) AS CLAIM_QTY,
DECODE(D.CLAIM_GB, '30', D.RSALE_AMT + ROUND(((D.DC_AMT - D.REMARK3_N)/D.CLAIM_QTY)*D.CLAIM_QTY), 0) AS CLAIM_AMT,
DECODE(D.CLAIM_GB, '30', D.RSALE_NET + DECODE(D.RSALE_VAT,0,ROUND(((D.DC_AMT - D.REMARK3_N)/D.CLAIM_QTY)*D.CLAIM_QTY), ROUND(((D.DC_AMT - D.REMARK3_N)/D.CLAIM_QTY)*D.CLAIM_QTY/1.1,0)), 0) AS CLAIM_NET,
DECODE(D.CLAIM_GB, '31', DECODE(D.ORDER_D_SEQ, '001', D.CLAIM_QTY, 0), 0) AS CLAIM_CAN_QTY,
DECODE(D.CLAIM_GB, '31', D.RSALE_AMT + ROUND(((D.DC_AMT - D.REMARK3_N)/D.CLAIM_QTY)*D.CLAIM_QTY), 0) AS CLAIM_CAN_AMT,
DECODE(D.CLAIM_GB, '31', D.RSALE_NET + DECODE(D.RSALE_VAT,0,ROUND(((D.DC_AMT - D.REMARK3_N)/D.CLAIM_QTY)*D.CLAIM_QTY), ROUND(((D.DC_AMT - D.REMARK3_N)/D.CLAIM_QTY)*D.CLAIM_QTY/1.1,0)), 0) AS CLAIM_CAN_NET
FROM TCLAIMDT D, TORDERM M, TMEDIA A, TGOODS G
WHERE D.CLAIM_DATE > = TRUNC(SYSDATE)
AND D.CLAIM_DATE < = SYSDATE
AND D.GOODS_CODE = G.GOODS_CODE
AND D.ORDER_NO = M.ORDER_NO
AND D.MEDIA_CODE = A.MEDIA_CODE
AND G.GIFT_YN = '0'
AND D.MEDIA_CODE IN (SELECT REMARK1 FROM TCODE WHERE CODE_LGROUP = 'O501')
) S
GROUP BY S.GATHER_DATE ,
S.ORDER_MEDIA ,
S.MEDIA_GB ,
S.MEDIA_CODE ,
S.LGROUP
) A
WHERE A.GATHER_DATE > = TO_DATE( '2021-04-17' /**P*/, 'yyyy-mm-dd')
AND A.GATHER_DATE < TO_DATE( '2021-04-23' /**P*/, 'yyyy-mm-dd') + INTERVAL '1' DAY
GROUP BY TO_CHAR(A.GATHER_DATE, 'yyyy-mm-dd'), A.MEDIA_CODE
) A
-- GROUP BY A.DAY_DD, A.MEDIA_CODE, MEDIA_CODE_NAME
GROUP BY GROUPING SETS((A.DAY_DD, A.MEDIA_CODE, MEDIA_CODE_NAME), DAY_DD,())
-- ORDER BY A.DAY_DD DESC, A.MEDIA_CODE DESC, MEDIA_CODE_NAME
-- GROUP BY GROUPING SETS((B.SALE_QTY, B.SALE_AMT, B.ORDER_QTY, B.ORDER_AMT, MEDIA_CODE, DAY_CNT), DAY_DD)
)
ORDER BY DAY_DD DESC, RESULT DESC, ORDER_AMT DESC
현재 이 쿼리를 실행하면 총계, 소계 등등이 다 표시가 됩니다. 근데 같은 모양의 쿼리인데 날짜만 다른 기간으로 해서 똑같이 총계, 소계 등등을 가져오는데 그 걸 옆으로 붙이고 싶어서요. ORDER BY DAY_DD DESC, RESULT DESC, ORDER_AMT DESC부분까지 복사해서 union all 하니 밑으로 붙어서요 현재 이 쿼리를 실행하면 첨부파일의 하단 보라색 테이블의 왼쪽처럼 그렇게 결과가 나오는데... 최종으론 왼쪽 옆에 똑같은 컬럼들인데 날짜가 다른 걸 조회해서 비교가 되게 오른쪽으로 결과값들을 붙여서 나오는 형태를 쿼리로 만들고 싶은데요.... 어떻게 바꿔야 할까요?
by 고등어통조림
[2021.04.26 09:55:10]
4~325 줄까지 복사해서 union all 로 붙이면 밑으로 붙습니다... 옆으로 붙이고 싶어요
) 버튼을 클릭하여 작성 하시면 됩니다.