데이터베이스 질문과 답변
A 쿼리와 B 쿼리를 union all 해서 첨부한 사진과 같이 결과가 나왔는데요
6~11번 줄의 결과값을 가로로 나열하고싶은데 혹시 방법이 있을까요?
아시는분은 힌트라도 주시면 감사하겠습니다ㅜ
by 마농
[2021.12.02 11:09:21]
쿼리를 보여주세요.
by 초보
[2021.12.02 11:14:31]
WITH W_STYLE AS(
SELECT Info.division_cd
,Info.style_cd
,MAX(Info.SECTION01_CD) SECTION01_CD
,MAX(Info.SECTION02_CD) SECTION02_CD
,MAX(Info.TAG_PRI) TAG_PRI
,MAX(PR.FIRST_PRI) FIRST_PRI
FROM biz_styleinfo Info,
BIZ_STYLEPRICEINFO PR
WHERE Info.division_cd = '0'
AND PR.DIVISION_CD = Info.DIVISION_CD
AND PR.STYLE_CD = Info.STYLE_CD
GROUP BY Info.division_cd, Info.style_cd ), W_SHOP AS (
SELECT Info.division_cd, NVL(A.SEQ_NO,'990') SEQ_NO ,INFO.SHOPTYPE
,Info.client_cd
,MAX(Info.clientnickname) clientnickname
FROM
COMM_CODE VIEW_shoptype_Name ,
COMM_CODE VIEW_shop_level_Name,
(
SELECT INFO.DIVISION_CD DIVISION_CD
,INFO.User_cd CODE
,MAX(USER_NAME.NAME) CODENAME
FROM COMM_CLIENTINFO INFO ,comm_user USER_NAME
WHERE INFO.User_cd IS NOT NULL
AND INFO.client_gu = 'M'
AND USER_NAME.DIVISION_CD (+) = INFO.DIVISION_CD
AND USER_NAME.USER_CD (+) = INFO.User_cd
GROUP BY INFO.DIVISION_CD
,INFO.User_cd
) VIEW_user_cd_Name ,
(SELECT DIVISION_CD DIVISION_CD ,CLIENT_CD CODE ,CLIENTNICKNAME CODENAME FROM COMM_CLIENTINFO WHERE CLIENT_GU = 'M' GROUP BY DIVISION_CD ,CLIENT_CD ,CLIENTNICKNAME ) VIEW_cg_Name ,
COMM_CODE VIEW_shop_area_Name ,
COMM_CLIENTINFO INFO
,BIZ_SHOP_ORDERBY A
WHERE Info.division_cd = '0'
AND Info.client_Gu = 'M'
AND VIEW_shoptype_Name .DIVISION_CD(+) = Info.DIVISION_CD AND VIEW_shoptype_Name .GU(+) = 'SHTYP' AND VIEW_shoptype_Name .CODE(+) = Info.shoptype
AND VIEW_shop_level_Name.DIVISION_CD(+) = Info.DIVISION_CD AND VIEW_shop_level_Name .GU(+) = 'SHOPL' AND VIEW_shop_level_Name.CODE(+) = Info.shop_level
AND VIEW_user_cd_Name .DIVISION_CD(+) = Info.DIVISION_CD AND VIEW_user_cd_Name .CODE(+) = Info.user_cd
AND VIEW_cg_Name .DIVISION_CD(+) = Info.DIVISION_CD AND VIEW_cg_Name .CODE(+) = Info.cg_cd
AND VIEW_shop_area_Name .DIVISION_CD(+) = Info.DIVISION_CD AND VIEW_shop_area_Name .GU(+) = 'SHOPA' AND VIEW_shop_area_Name .CODE(+) = Info.shop_area
AND A.DIVISION_CD (+) = Info.DIVISION_CD
AND A.SHOP_CD (+) = Info.CLIENT_CD
GROUP BY Info.DIVISION_CD,Info.CLIENT_CD ,A.SEQ_NO ,INFO.SHOPTYPE)
,W_SALE_WC AS(
SELECT B.DIVISION_CD
,B.SHOP_CD
,W_STYLE.SECTION01_CD
,W_STYLE.SECTION02_CD
,B.SALESECTION_CD SALESECTION_CD
,ROUND(DECODE(B.NOW_AMT,0,0, 100-(B.REAL_AMT/B.NOW_AMT)*100)) DC_PER
,SUM(DECODE(B.WORK_GU, '0', B.REAL_AMT, -B.REAL_AMT)) SALE_AMT
FROM W_SHOP W_SHOP
,W_STYLE W_STYLE
,BIZ_SALE B
WHERE B.DIVISION_CD = '0'
AND B.DIVISION_CD = W_STYLE.DIVISION_CD
AND B.STYLE_CD = W_STYLE.STYLE_CD
AND B.SALE_DT BETWEEN '20211125' AND '20211125'
AND W_SHOP.DIVISION_CD = B.DIVISION_CD
AND W_SHOP.client_cd = B.SHOP_CD
GROUP BY B.DIVISION_CD
,B.SHOP_CD
,W_STYLE.SECTION01_CD
,W_STYLE.SECTION02_CD
,B.SALESECTION_CD
,ROUND(DECODE(B.NOW_AMT,0,0, 100-(B.REAL_AMT/B.NOW_AMT)*100))
)
,W_SALE_WB AS(
SELECT B.DIVISION_CD
,B.SHOP_CD
,W_STYLE.SECTION01_CD
,W_STYLE.SECTION02_CD
,B.SALESECTION_CD SALESECTION_CD
,ROUND(DECODE(B.NOW_AMT,0,0, 100-(B.REAL_AMT/B.NOW_AMT)*100)) DC_PER
,SUM(DECODE(B.WORK_GU, '0', B.REAL_AMT, -B.REAL_AMT)) SALE_AMT
FROM W_SHOP W_SHOP
,W_STYLE W_STYLE
,BIZ_SALE B
WHERE B.DIVISION_CD = '0'
AND B.DIVISION_CD = W_STYLE.DIVISION_CD
AND B.STYLE_CD = W_STYLE.STYLE_CD
AND B.SALE_DT BETWEEN '20191125' AND '20191130'
AND W_SHOP.DIVISION_CD = B.DIVISION_CD
AND W_SHOP.client_cd = B.SHOP_CD
GROUP BY B.DIVISION_CD
,B.SHOP_CD
,W_STYLE.SECTION01_CD
,W_STYLE.SECTION02_CD
,B.SALESECTION_CD
,ROUND(DECODE(B.NOW_AMT,0,0, 100-(B.REAL_AMT/B.NOW_AMT)*100))
)
--SELECT * FROM (
SELECT *
FROM (
SELECT
CASE
WHEN shop_type LIKE '정장' THEN '사업부'
WHEN shop_type LIKE '캐주얼%' THEN '사업부'
WHEN shop_type LIKE '이비즈' THEN '사업부'
WHEN shop_type LIKE 'S.P.O.T' THEN '사업부'
WHEN shop_type LIKE 'O.V.C' THEN '사업부'
ELSE '총 계' end as "DIVISION"
, shop_type SHOP
,'' TARGET
, SUM(real_amt ) 합계
, SUM(normal_amt) 정상
, SUM(gi_amt ) 이월
,'' ACHIEVE
FROM (
SELECT '1' NUM
,W_SHOP.SEQ_NO SEQ_NO
,CASE
WHEN A.SHOP_CD LIKE 'P%' THEN '정장'
WHEN A.SHOP_CD LIKE 'C%' THEN '캐주얼백화점'
WHEN A.SHOP_CD LIKE 'E%' THEN '이비즈'
WHEN A.SHOP_CD LIKE 'X%' THEN 'S.P.O.T'
WHEN A.SHOP_CD LIKE 'O%' THEN 'O.V.C' END AS "SHOP_TYPE"
,SUM(A.TOT_AMT) REAL_AMT
,SUM(A.TOT_AMT - A.GI_AMT) NORMAL_AMT
,SUM(A.GI_AMT) GI_AMT
FROM (
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WB.SECTION01_CD SECTION01_CD
,W_SALE_WB.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,SUM(W_SALE_WB.SALE_AMT) TOT_AMT
,0 JS_AMT
,0 M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WB.SECTION01_CD
,W_SALE_WB.SECTION02_CD
,W_SHOP.SHOPTYPE
UNION ALL
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WB.SECTION01_CD SECTION01_CD
,W_SALE_WB.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WB.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.DC_PER BETWEEN 1 AND 19
AND (W_SALE_WB.SALESECTION_CD <> '30')
AND (W_SALE_WB.SALESECTION_CD <> '40')
AND (W_SALE_WB.SALESECTION_CD <> '50')
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WB.SECTION01_CD
,W_SALE_WB.SECTION02_CD
,W_SHOP.SHOPTYPE
UNION ALL
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WB.SECTION01_CD SECTION01_CD
,W_SALE_WB.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WB.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.DC_PER BETWEEN 20 AND 39
AND (W_SALE_WB.SALESECTION_CD <> '30')
AND (W_SALE_WB.SALESECTION_CD <> '40')
AND (W_SALE_WB.SALESECTION_CD <> '50')
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WB.SECTION01_CD
,W_SALE_WB.SECTION02_CD
,W_SHOP.SHOPTYPE
UNION ALL
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WB.SECTION01_CD SECTION01_CD
,W_SALE_WB.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WB.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.DC_PER BETWEEN 40 AND 59
AND (W_SALE_WB.SALESECTION_CD <> '30')
AND (W_SALE_WB.SALESECTION_CD <> '40')
AND (W_SALE_WB.SALESECTION_CD <> '50')
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WB.SECTION01_CD
,W_SALE_WB.SECTION02_CD
,W_SHOP.SHOPTYPE
UNION ALL
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WB.SECTION01_CD SECTION01_CD
,W_SALE_WB.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WB.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.DC_PER >= 60
AND (W_SALE_WB.SALESECTION_CD <> '30')
AND (W_SALE_WB.SALESECTION_CD <> '40')
AND (W_SALE_WB.SALESECTION_CD <> '50')
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WB.SECTION01_CD
,W_SALE_WB.SECTION02_CD
,W_SHOP.SHOPTYPE
UNION ALL
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WB.SECTION01_CD SECTION01_CD
,W_SALE_WB.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WB.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.SALESECTION_CD = '40'
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WB.SECTION01_CD
,W_SALE_WB.SECTION02_CD
,W_SHOP.SHOPTYPE
UNION ALL
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WB.SECTION01_CD SECTION01_CD
,W_SALE_WB.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WB.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.SALESECTION_CD = '50'
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WB.SECTION01_CD
,W_SALE_WB.SECTION02_CD
,W_SHOP.SHOPTYPE
UNION ALL
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WB.SECTION01_CD SECTION01_CD
,W_SALE_WB.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WB.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.SALESECTION_CD = '30'
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WB.SECTION01_CD
,W_SALE_WB.SECTION02_CD
,W_SHOP.SHOPTYPE
) A ,W_SHOP
,COMM_CODE S_GU1
,COMM_CODE S_GU2
,COMM_CODE S_TYP
WHERE W_SHOP.CLIENT_CD = A.SHOP_CD
AND S_GU1.DIVISION_CD (+) = '0'
AND S_GU1.GU (+) = 'S_GU1'
AND S_GU1.CODE (+) = A.SECTION01_CD
AND S_GU2.DIVISION_CD (+) = '0'
AND S_GU2.GU (+) = 'S_GU2'
AND S_GU2.CODE (+) = A.SECTION02_CD
AND S_TYP.DIVISION_CD (+) = '0'
AND S_TYP.GU (+) = 'SHTYP'
AND S_TYP.CODE (+) = A.SHOPTYPE
GROUP BY W_SHOP.SEQ_NO
,A.SHOP_CD
UNION ALL
SELECT '2' NUM
,'999' SEQ_NO
,'' SHOP_TYPE
,SUM(A.TOT_AMT) REAL_AMT
,SUM(A.TOT_AMT - A.GI_AMT) NORMAL_AMT
,SUM(A.GI_AMT) GI_AMT
FROM (
SELECT W_SALE_WB.SECTION01_CD SECTION01_CD
,SUM(W_SALE_WB.SALE_AMT) TOT_AMT
,0 JS_AMT
,0 M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
GROUP BY W_SALE_WB.SECTION01_CD
UNION ALL
SELECT W_SALE_WB.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,SUM(W_SALE_WB.SALE_AMT) JS_AMT
,0 M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.DC_PER < 1
AND (W_SALE_WB.SALESECTION_CD <> '30')
AND (W_SALE_WB.SALESECTION_CD <> '40')
AND (W_SALE_WB.SALESECTION_CD <> '50')
GROUP BY W_SALE_WB.SECTION01_CD
UNION ALL
SELECT W_SALE_WB.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WB.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.DC_PER BETWEEN 1 AND 19
AND (W_SALE_WB.SALESECTION_CD <> '30')
AND (W_SALE_WB.SALESECTION_CD <> '40')
AND (W_SALE_WB.SALESECTION_CD <> '50')
GROUP BY W_SALE_WB.SECTION01_CD
UNION ALL
SELECT W_SALE_WB.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,0 JS_AMT
,0 M20_AMT
,SUM(W_SALE_WB.SALE_AMT) M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.DC_PER BETWEEN 20 AND 39
AND (W_SALE_WB.SALESECTION_CD <> '30')
AND (W_SALE_WB.SALESECTION_CD <> '40')
AND (W_SALE_WB.SALESECTION_CD <> '50')
GROUP BY W_SALE_WB.SECTION01_CD
UNION ALL
SELECT W_SALE_WB.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,0 JS_AMT
,0 M20_AMT
,0 M30_AMT
,SUM(W_SALE_WB.SALE_AMT) M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.DC_PER BETWEEN 40 AND 59
AND (W_SALE_WB.SALESECTION_CD <> '30')
AND (W_SALE_WB.SALESECTION_CD <> '40')
AND (W_SALE_WB.SALESECTION_CD <> '50')
GROUP BY W_SALE_WB.SECTION01_CD
UNION ALL
SELECT W_SALE_WB.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,0 JS_AMT
,0 M20_AMT
,0 M30_AMT
,0 M50_AMT
,SUM(W_SALE_WB.SALE_AMT) M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.DC_PER >= 60
AND (W_SALE_WB.SALESECTION_CD <> '30')
AND (W_SALE_WB.SALESECTION_CD <> '40')
AND (W_SALE_WB.SALESECTION_CD <> '50')
GROUP BY W_SALE_WB.SECTION01_CD
UNION ALL
SELECT W_SALE_WB.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,0 JS_AMT
,0 M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,SUM(W_SALE_WB.SALE_AMT) GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.SALESECTION_CD = '40'
GROUP BY W_SALE_WB.SECTION01_CD
UNION ALL
SELECT W_SALE_WB.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,0 JS_AMT
,0 M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,SUM(W_SALE_WB.SALE_AMT) GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.SALESECTION_CD = '50'
GROUP BY W_SALE_WB.SECTION01_CD
UNION ALL
SELECT W_SALE_WB.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,0 JS_AMT
,0 M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,SUM(W_SALE_WB.SALE_AMT) IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WB
WHERE W_SHOP.CLIENT_CD = W_SALE_WB.SHOP_CD
AND W_SALE_WB.SALESECTION_CD = '30'
GROUP BY W_SALE_WB.SECTION01_CD
) A, COMM_CODE S_GU1
WHERE S_GU1.DIVISION_CD (+) = '0'
AND S_GU1.GU (+) = 'S_GU1'
AND S_GU1.CODE (+) = A.SECTION01_CD
ORDER BY 1,4
)
GROUP BY shop_type
) A
UNION ALL
SELECT *
FROM (
SELECT
CASE
WHEN shop_type LIKE '정장' THEN '사업부'
WHEN shop_type LIKE '캐주얼%' THEN '사업부'
WHEN shop_type LIKE '이비즈' THEN '사업부'
WHEN shop_type LIKE 'S.P.O.T' THEN '사업부'
WHEN shop_type LIKE 'O.V.C' THEN '사업부'
ELSE '총 계' end as "DIVISION"
, shop_type SHOP
,'' TARGET
, SUM(real_amt ) 합계
, SUM(normal_amt) 정상
, SUM(gi_amt ) 이월
,'' ACHIEVE
FROM (
SELECT '1' NUM
,W_SHOP.SEQ_NO SEQ_NO
,CASE
WHEN A.SHOP_CD LIKE 'P%' THEN '정장'
WHEN A.SHOP_CD LIKE 'C%' THEN '캐주얼백화점'
WHEN A.SHOP_CD LIKE 'E%' THEN '이비즈'
WHEN A.SHOP_CD LIKE 'X%' THEN 'S.P.O.T'
WHEN A.SHOP_CD LIKE 'O%' THEN 'O.V.C' END AS "SHOP_TYPE"
,SUM(A.TOT_AMT) REAL_AMT
,SUM(A.TOT_AMT - A.GI_AMT) NORMAL_AMT
,SUM(A.GI_AMT) GI_AMT
FROM (
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WC.SECTION01_CD SECTION01_CD
,W_SALE_WC.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,SUM(W_SALE_WC.SALE_AMT) TOT_AMT
,0 JS_AMT
,0 M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WC.SECTION01_CD
,W_SALE_WC.SECTION02_CD
,W_SHOP.SHOPTYPE
UNION ALL
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WC.SECTION01_CD SECTION01_CD
,W_SALE_WC.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WC.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.DC_PER BETWEEN 1 AND 19
AND (W_SALE_WC.SALESECTION_CD <> '30')
AND (W_SALE_WC.SALESECTION_CD <> '40')
AND (W_SALE_WC.SALESECTION_CD <> '50')
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WC.SECTION01_CD
,W_SALE_WC.SECTION02_CD
,W_SHOP.SHOPTYPE
UNION ALL
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WC.SECTION01_CD SECTION01_CD
,W_SALE_WC.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WC.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.DC_PER BETWEEN 20 AND 39
AND (W_SALE_WC.SALESECTION_CD <> '30')
AND (W_SALE_WC.SALESECTION_CD <> '40')
AND (W_SALE_WC.SALESECTION_CD <> '50')
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WC.SECTION01_CD
,W_SALE_WC.SECTION02_CD
,W_SHOP.SHOPTYPE
UNION ALL
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WC.SECTION01_CD SECTION01_CD
,W_SALE_WC.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WC.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.DC_PER BETWEEN 40 AND 59
AND (W_SALE_WC.SALESECTION_CD <> '30')
AND (W_SALE_WC.SALESECTION_CD <> '40')
AND (W_SALE_WC.SALESECTION_CD <> '50')
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WC.SECTION01_CD
,W_SALE_WC.SECTION02_CD
,W_SHOP.SHOPTYPE
UNION ALL
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WC.SECTION01_CD SECTION01_CD
,W_SALE_WC.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WC.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.DC_PER >= 60
AND (W_SALE_WC.SALESECTION_CD <> '30')
AND (W_SALE_WC.SALESECTION_CD <> '40')
AND (W_SALE_WC.SALESECTION_CD <> '50')
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WC.SECTION01_CD
,W_SALE_WC.SECTION02_CD
,W_SHOP.SHOPTYPE
UNION ALL
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WC.SECTION01_CD SECTION01_CD
,W_SALE_WC.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WC.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.SALESECTION_CD = '40'
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WC.SECTION01_CD
,W_SALE_WC.SECTION02_CD
,W_SHOP.SHOPTYPE
UNION ALL
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WC.SECTION01_CD SECTION01_CD
,W_SALE_WC.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WC.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.SALESECTION_CD = '50'
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WC.SECTION01_CD
,W_SALE_WC.SECTION02_CD
,W_SHOP.SHOPTYPE
UNION ALL
SELECT W_SHOP.CLIENT_CD SHOP_CD
,W_SALE_WC.SECTION01_CD SECTION01_CD
,W_SALE_WC.SECTION02_CD SECTION02_CD
,W_SHOP.SHOPTYPE SHOPTYPE
,MAX(W_SHOP.CLIENTNICKNAME) SHOP_NAME
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WC.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.SALESECTION_CD = '30'
GROUP BY W_SHOP.CLIENT_CD
,W_SALE_WC.SECTION01_CD
,W_SALE_WC.SECTION02_CD
,W_SHOP.SHOPTYPE
) A ,W_SHOP
,COMM_CODE S_GU1
,COMM_CODE S_GU2
,COMM_CODE S_TYP
WHERE W_SHOP.CLIENT_CD = A.SHOP_CD
AND S_GU1.DIVISION_CD (+) = '0'
AND S_GU1.GU (+) = 'S_GU1'
AND S_GU1.CODE (+) = A.SECTION01_CD
AND S_GU2.DIVISION_CD (+) = '0'
AND S_GU2.GU (+) = 'S_GU2'
AND S_GU2.CODE (+) = A.SECTION02_CD
AND S_TYP.DIVISION_CD (+) = '0'
AND S_TYP.GU (+) = 'SHTYP'
AND S_TYP.CODE (+) = A.SHOPTYPE
GROUP BY W_SHOP.SEQ_NO
,A.SHOP_CD
UNION ALL
SELECT '2' NUM
,'999' SEQ_NO
,'' SHOP_TYPE
,SUM(A.TOT_AMT) REAL_AMT
,SUM(A.TOT_AMT - A.GI_AMT) NORMAL_AMT
,SUM(A.GI_AMT) GI_AMT
FROM (
SELECT W_SALE_WC.SECTION01_CD SECTION01_CD
,SUM(W_SALE_WC.SALE_AMT) TOT_AMT
,0 JS_AMT
,0 M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
GROUP BY W_SALE_WC.SECTION01_CD
UNION ALL
SELECT W_SALE_WC.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,SUM(W_SALE_WC.SALE_AMT) JS_AMT
,0 M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.DC_PER < 1
AND (W_SALE_WC.SALESECTION_CD <> '30')
AND (W_SALE_WC.SALESECTION_CD <> '40')
AND (W_SALE_WC.SALESECTION_CD <> '50')
GROUP BY W_SALE_WC.SECTION01_CD
UNION ALL
SELECT W_SALE_WC.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,0 JS_AMT
,SUM(W_SALE_WC.SALE_AMT) M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.DC_PER BETWEEN 1 AND 19
AND (W_SALE_WC.SALESECTION_CD <> '30')
AND (W_SALE_WC.SALESECTION_CD <> '40')
AND (W_SALE_WC.SALESECTION_CD <> '50')
GROUP BY W_SALE_WC.SECTION01_CD
UNION ALL
SELECT W_SALE_WC.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,0 JS_AMT
,0 M20_AMT
,SUM(W_SALE_WC.SALE_AMT) M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.DC_PER BETWEEN 20 AND 39
AND (W_SALE_WC.SALESECTION_CD <> '30')
AND (W_SALE_WC.SALESECTION_CD <> '40')
AND (W_SALE_WC.SALESECTION_CD <> '50')
GROUP BY W_SALE_WC.SECTION01_CD
UNION ALL
SELECT W_SALE_WC.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,0 JS_AMT
,0 M20_AMT
,0 M30_AMT
,SUM(W_SALE_WC.SALE_AMT) M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.DC_PER BETWEEN 40 AND 59
AND (W_SALE_WC.SALESECTION_CD <> '30')
AND (W_SALE_WC.SALESECTION_CD <> '40')
AND (W_SALE_WC.SALESECTION_CD <> '50')
GROUP BY W_SALE_WC.SECTION01_CD
UNION ALL
SELECT W_SALE_WC.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,0 JS_AMT
,0 M20_AMT
,0 M30_AMT
,0 M50_AMT
,SUM(W_SALE_WC.SALE_AMT) M60_AMT
,0 IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.DC_PER >= 60
AND (W_SALE_WC.SALESECTION_CD <> '30')
AND (W_SALE_WC.SALESECTION_CD <> '40')
AND (W_SALE_WC.SALESECTION_CD <> '50')
GROUP BY W_SALE_WC.SECTION01_CD
UNION ALL
SELECT W_SALE_WC.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,0 JS_AMT
,0 M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,SUM(W_SALE_WC.SALE_AMT) GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.SALESECTION_CD = '40'
GROUP BY W_SALE_WC.SECTION01_CD
UNION ALL
SELECT W_SALE_WC.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,0 JS_AMT
,0 M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,0 IH_AMT
,0 GI_AMT
,SUM(W_SALE_WC.SALE_AMT) GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.SALESECTION_CD = '50'
GROUP BY W_SALE_WC.SECTION01_CD
UNION ALL
SELECT W_SALE_WC.SECTION01_CD SECTION01_CD
,0 TOT_AMT
,0 JS_AMT
,0 M20_AMT
,0 M30_AMT
,0 M50_AMT
,0 M60_AMT
,SUM(W_SALE_WC.SALE_AMT) IH_AMT
,0 GI_AMT
,0 GH_AMT
FROM W_SHOP, W_SALE_WC
WHERE W_SHOP.CLIENT_CD = W_SALE_WC.SHOP_CD
AND W_SALE_WC.SALESECTION_CD = '30'
GROUP BY W_SALE_WC.SECTION01_CD
) A, COMM_CODE S_GU1
WHERE S_GU1.DIVISION_CD (+) = '0'
AND S_GU1.GU (+) = 'S_GU1'
AND S_GU1.CODE (+) = A.SECTION01_CD
ORDER BY 1,4
)
GROUP BY shop_type
) B
)
--UNPIVOT (RESULT FOR GB1 IN (정상, 이월, 합계))
by 초보
[2021.12.02 11:16:09]
미숙해서 불필요한게 많이 보일수있는데 양해부탁드리겠습니다..ㅜㅜ
by 마농
[2021.12.02 12:33:06]
1. with 문이 w_sale_XX 형태가 많은데 똑같은 쿼리 이름만 다르고?
- 결국 쓰는건 w_sale_wb, w_sale_wc 두개 뿐이네요?
- 안쓰는 with 문은 제거가 필요합니다.
2. w_sale_wb, w_sale_wc 는 동일한 쿼리에 기간만 다른 건가요?
- w_sale_wb : '20191125' ~ '20191130'
- w_sale_wc : '20211125' ~ '20211125'
- 기간 조건 정확하게 준것 맞나요?
by 초보
[2021.12.02 13:44:49]
말씀 감사합니다!
1. 나머지 WITH문도 후에 쿼리를 만들어서 이어붙이려고 만들어두었습니다. 현 시점에선 사용하지 않아 다시 수정했습니다!
2. 의도하고자 했던건 w_sale_wb : '20191125' ~ '20191130', w_sale_wc : '20211125' ~ '20211125', w_sale_wl : '20201125' ~ '20201125' 이런식으로 각각 다른 기간의 매장별 정상, 이월, 합계 내역을 보고서처럼 가로로 나열하려고 했는데 다른 기간으로 조회한 SQL문이 세로로 붙어서 나오네요
by 마농
[2021.12.02 14:15:38]
1. 왜 기간이 조금씩 다르죠?
- 25일이면 25일로 통일해야 하는데 왜 하나는 30일까지 인지?
- 오타인지? 의도가 있는 건지?
- 의도가 있다면. 무슨 의도인지?
2. 쿼리에 군더더기가 너무 많네요.
- 사용하지 않는 컬럼들
- 불필요해 보이는 조인과 Group by
3. 하나의 테이블을 반복적으로 사용
- 조건이 다르다고 해서 쿼리를 따로따로 만들어 붙이고 있습니다.
- 이러면 쿼리가 계속 길어질수밖에 없습니다.
- 공통조건은 WHERE 절로 빼고
- 개별조건은 CASE 문을 이용하면 개별 쿼리를 하나의 쿼리로 만들 수 있습니다.
by 초보
[2021.12.02 14:33:44]
1. 기간은 그저 임의로 검색한 값에서 나오는 데이터를 정렬하고싶어서 아무 값이나 넣어보았습니다
2,3. 말씀해주신 부분들 수정해보겠습니다! 정말 감사합니다
by 마농
[2021.12.02 14:50:17]
실질적 요구사항을 보면 몇줄 안되는 쿼리문으로 구현 가능한 간단한 부분입니다.
기존 쿼리 분석하다거 시간 다 갈 것 같네요.
처음 부터 새로 짜는게 좋을 것 같긴 한데요.
그러려면 테이블 구조 및 요구사항을 명확하게 알아야 합니다.
그냥 아무거나 입력해 봤다는 식의 질문은 안됩니다.
정확한 의도와 정확한 값으로 질문해 주셔야 합니다.
아무거나 입력한 기간 때문에 이게 도대체 어떤 의도일까? 저는 한참을 고민합니다.
by 마농
[2021.12.02 17:04:44]
-- 간단한 예제 쿼리입니다. --
WITH test AS
(
SELECT '사업부' division
, DECODE(SUBSTR(shop_cd, 1, 1), 'P', '정장'
, 'C', '캐주얼백화점'
, 'E', '이비즈'
, 'X', 'S.P.O.T'
, 'O', 'O.V.C'
, '기타'
) shoptype
, DECODE(work_gu, '0', real_amt, -real_amt) sale_amt
, CASE WHEN sale_dt BETWEEN '20201125' AND '20201125' THEN 1
WHEN sale_dt BETWEEN '20211125' AND '20211125' THEN 2
END gb
, salesection_cd
FROM biz_sale
WHERE division_cd = '0'
AND ( sale_dt BETWEEN '20201125' AND '20201125' -- 1번 기간
OR sale_dt BETWEEN '20211125' AND '20211125' -- 2번 기간
)
)
SELECT division
, NVL(shoptype, '총계') shoptype
, SUM(CASE WHEN gb = 1 THEN amt END) real_amt_1
, SUM(CASE WHEN gb = 1 AND salesection_cd != '40' THEN amt END) normal_amt_1
, SUM(CASE WHEN gb = 1 AND salesection_cd = '40' THEN amt END) gi_amt_1
, SUM(CASE WHEN gb = 2 THEN amt END) real_amt_2
, SUM(CASE WHEN gb = 2 AND salesection_cd != '40' THEN amt END) normal_amt_2
, SUM(CASE WHEN gb = 2 AND salesection_cd = '40' THEN amt END) gi_amt_2
FROM test
GROUP BY division, ROLLUP(shoptype)
;
by 초보
[2021.12.02 17:37:03]
너무 감사합니다! 참고해서 더 공부하겠습니다!
) 버튼을 클릭하여 작성 하시면 됩니다.