데이터베이스 질문과 답변
by 카리스박 [2017.07.30 21:42:02]
SELECT A.CD_YMD_DATE
,A.CD_COMPANY
,A.CD_ROOM
,A.NM_ROOM
FROM
(
SELECT A.DT_INDATE AS CD_YMD_DATE
,A.CD_COMPANY AS CD_COMPANY
,R.CD_ROOM AS CD_ROOM
,R.NM_ROOM AS NM_ROOM
FROM STG_ERP_CZ_POR A JOIN STG_ERP_CZ_ITEM I ON A.CD_COMPANY = I.CD_COMPANY AND A.CD_ITEM = I.CD_ITEM
LEFT OUTER JOIN STG_ERP_MA_CODEDTL L ON I.CD_COMPANY = L.CD_COMPANY AND I.CD_CLASSL = L.CD_SYSDEF AND L.CD_FIELD = 'CZ_WH00001'
LEFT OUTER JOIN STG_ERP_CZ_STOCK S ON A.CD_COMPANY = S.CD_COMPANY AND A.CD_ROOM = S.CD_ROOM AND A.CD_STOCK = S.CD_STOCK AND S.USE_YN = 'Y'
LEFT OUTER JOIN STG_ERP_CZ_ROOM R ON A.CD_COMPANY = R.CD_COMPANY AND A.CD_ROOM = R.CD_ROOM
WHERE A.CD_COMPANY = '1001'
GROUP BY A.DT_INDATE, R.CD_ROOM, R.NM_ROOM, A.CD_COMPANY
ORDER BY 1 DESC) A RIGHT OUTER JOIN STG_ERP_CZ_ROOM B ON A.CD_COMPANY = B.CD_COMPANY AND A.CD_ROOM = B.CD_ROOM
WHERE B.YN_USE = 'Y'
AND A.CD_YMD_DATE = '20170717';
이런 select문이 있을때 STG_ERP_CZ_POR와 STG_ERP_CZ_ROOM(전체room정보 테이블)를 비교해서 STG_ERP_CZ_ROOM의 모든 cd_room정보를 기준으로 STG_ERP_CZ_POR에 있는 cd_room이면 o표시를 없는 cd_room이면 x표시를 하는 열을 추가하고 싶습니다.
가능할까요?
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT DT_CLOSE AS CD_YMD_DATE
,CD_ROOM AS CD_ROOM
,COUNT(DECODE(INOUT,'02',1)) AS INOUT
,COUNT(DECODE(SALE,'02',1)) AS SALE
,COUNT(DECODE(SALE1,'02',1)) AS SALE1
,COUNT(DECODE(OPER,'02',1)) AS OPER
FROM
(
SELECT A.CD_COMPANY AS CD_COMPANY ,
A.CD_ROOM AS CD_ROOM,
A.NM_ROOM AS NM_ROOM,
A.YN_USE AS YN_USE,
B.DT_CLOSE AS DT_CLOSE,
B.GB_CLOSE AS GB_CLOSE,
NVL(B.INOUT,'01') as INOUT, --입고재고마감
NVL(B.SALE,'01') as SALE, --식권마감
NVL(B.SALE1,'01') as SALE1, --식수마감
NVL(B.OPER,'01') as OPER --운영일지
FROM STG_ERP_CZ_ROOM A LEFT OUTER JOIN STG_ERP_CZ_ROOM_CLOSE B
ON A.CD_COMPANY = B.CD_COMPANY
AND A.CD_ROOM = B.CD_ROOM
AND B.GB_CLOSE = '02'
)
WHERE CD_COMPANY = '1001'
AND YN_USE ='Y'
AND DT_CLOSE IS NOT NULL
GROUP BY DT_CLOSE, CD_ROOM
ORDER BY 1 DESC;
그리고 위에 있는 테이블과 밑에있는 테이블의 열 INOUT, SALE, SALE1, OPER를 옆으로 붙일 수 있을까요?
조인이 필요한지 의문이네요?
- 1. 전혀 사용되지 않는 테이블 : i
- 2. 아우터 조인 되고 있으나 특별한 역할이 없는 테이블 : l, s, r
SELECT b.cd_company
, b.cd_room
, b.nm_room
, a.cd_ymd_date
, NVL(a.ox, 'x' ) ox
, NVL(c.inout, 0) inout
, NVL(c.sale , 0) sale
, NVL(c.sale1, 0) sale1
, NVL(c.oper , 0) oper
FROM stg_erp_cz_room b
LEFT OUTER JOIN (SELECT DISTINCT -- 중복제거가 필요한지 확인 필요.
cd_ymd_date
, cd_company
, cd_room
, 'o' ox
FROM stg_erp_cz_por
WHERE cd_company = '1001'
AND cd_ymd_date = '20170717'
) a
ON a.cd_company = b.cd_company
AND a.cd_room = b.cd_room
LEFT OUTER JOIN (SELECT dt_close
, cd_company
, cd_room
, COUNT(DECODE(inout, '02', 1)) AS inout
, COUNT(DECODE(sale , '02', 1)) AS sale
, COUNT(DECODE(sale1, '02', 1)) AS sale1
, COUNT(DECODE(oper , '02', 1)) AS oper
FROM stg_erp_cz_room_close
WHERE cd_company = '1001'
AND dt_close = '20170717'
AND gb_close = '02'
GROUP BY dt_close, cd_company, cd_room
) c
ON b.cd_company = c.cd_company
AND b.cd_room = c.cd_room
WHERE b.cd_company = '1001'
AND b.yn_use = 'Y'
ORDER BY cd_company, cd_room
;
네 답변감사합니다.
확인해보니 말씀해주신대로 i, l, s, r테이블은 조인안해도 되는 테이블이네요
그리고 조회해보면 stg_erp_cz_por에 없는 room에 관해서도 stg_erp_cz_por의 조건절의 cd_ymd_date의 날짜로 강제로 입력할 수 있는 방법이 있을까요?
SELECT '20170717' cd_ymd_date
, b.cd_company
, b.cd_room
, b.nm_room
, NVL2(a.cd_room, 'o', 'x') ox
, NVL(c.inout, 0) inout
, NVL(c.sale , 0) sale
, NVL(c.sale1, 0) sale1
, NVL(c.oper , 0) oper
FROM stg_erp_cz_room b
LEFT OUTER JOIN (SELECT DISTINCT -- 중복제거가 필요한지 확인 필요.
cd_company
, cd_room
FROM stg_erp_cz_por
WHERE cd_company = '1001'
AND cd_ymd_date = '20170717'
) a
ON b.cd_company = a.cd_company
AND b.cd_room = a.cd_room
LEFT OUTER JOIN (SELECT cd_company
, cd_room
, COUNT(DECODE(inout, '02', 1)) AS inout
, COUNT(DECODE(sale , '02', 1)) AS sale
, COUNT(DECODE(sale1, '02', 1)) AS sale1
, COUNT(DECODE(oper , '02', 1)) AS oper
FROM stg_erp_cz_room_close
WHERE cd_company = '1001'
AND dt_close = '20170717'
AND gb_close = '02'
GROUP BY cd_company, cd_room
) c
ON b.cd_company = c.cd_company
AND b.cd_room = c.cd_room
WHERE b.cd_company = '1001'
AND b.yn_use = 'Y'
ORDER BY cd_company, cd_room
;
재질문드려서 죄송합니다.
강제로 입력하는 것이 아니라 stg_erp_cz_por에 없는 room에 관해서도 stg_erp_cz_por의 조건절의 cd_ymd_date의 날짜를 가져올 수 있을까요?
글쎄요? 뭐가 문제인지?
질문의 의도가 뭔가요?
원하는 결과가 안나오나요?
답변감사합니다.
다름이 아니라 최종적으로 NULL인 CD_YMD_DATE의 값도 STG_ERP_CZ_POR에 있는 CD_YMD_DATE의 날짜값을 가지고 오고 싶어서요
음.. 그렇게 해 드린건데요.
입력조건값을 그대로 Select 절에서 사용하면 원하시는 결과일 텐데요?
결과에 문제가 있나요?
SELECT b.cd_company
, b.cd_room
, b.nm_room
, a.cd_ymd_date
, NVL(a.ox, 'x' ) ox
, NVL(c.inout, 0) inout
, NVL(c.sale , 0) sale
, NVL(c.sale1, 0) sale1
, NVL(c.oper , 0) oper
FROM stg_erp_cz_room b
LEFT OUTER JOIN (SELECT DISTINCT
dt_por cd_ymd_date
, cd_company
, cd_room
, 'o' ox
FROM stg_erp_cz_por
WHERE cd_company = '1001'
ORDER BY 1 desc
) a
ON a.cd_company = b.cd_company
AND a.cd_room = b.cd_room
LEFT OUTER JOIN (SELECT dt_close cd_ymd_date
, cd_company
, cd_room
, COUNT(DECODE(inout, '02', 1)) AS inout
, COUNT(DECODE(sale , '02', 1)) AS sale
, COUNT(DECODE(sale1, '02', 1)) AS sale1
, COUNT(DECODE(oper , '02', 1)) AS oper
FROM stg_erp_cz_room_close
WHERE cd_company = '1001'
AND gb_close = '02'
GROUP BY dt_close, cd_company, cd_room
ORDER BY 1 desc
) c
ON b.cd_company = c.cd_company
AND b.cd_room = c.cd_room
WHERE b.cd_company = '1001'
AND b.yn_use = 'Y'
ORDER BY cd_ymd_date desc, cd_room
아..전체날짜를 가지고 올때 그날짜에 stg_erp_cz_por에 없는 room에 관해서도 stg_erp_cz_room과 outer조인하여 stg_erp_cz_por의 cd_ymd_date의 날짜를 가져올 수 있는지가 궁금합니다.
우선. Distinct 사용은 적절한가요?
SELECT d.cd_ymd_date
, b.cd_company
, b.cd_room
, b.nm_room
, NVL(d.ox, 'x' ) ox
, NVL(d.inout, 0) inout
, NVL(d.sale , 0) sale
, NVL(d.sale1, 0) sale1
, NVL(d.oper , 0) oper
FROM stg_erp_cz_room b
LEFT OUTER JOIN
(SELECT NVL(a.dt_por , c.dt_close ) cd_ymd_date
, NVL(a.cd_company, c.cd_company) cd_company
, NVL(a.cd_room , c.cd_room ) cd_room
, a.ox
, c.inout
, c.sale
, c.sale1
, c.oper
FROM (SELECT DISTINCT
dt_por
, cd_company
, cd_room
, 'o' ox
FROM stg_erp_cz_por
WHERE cd_company = '1001'
) a
FULL OUTER JOIN
(SELECT dt_close
, cd_company
, cd_room
, COUNT(DECODE(inout, '02', 1)) AS inout
, COUNT(DECODE(sale , '02', 1)) AS sale
, COUNT(DECODE(sale1, '02', 1)) AS sale1
, COUNT(DECODE(oper , '02', 1)) AS oper
FROM stg_erp_cz_room_close
WHERE cd_company = '1001'
AND gb_close = '02'
GROUP BY dt_close, cd_company, cd_room
) c
ON a.dt_por = c.dt_close
AND a.cd_company = c.cd_company
AND a.cd_room = c.cd_room
) d
PARTITION BY (d.cd_ymd_date)
ON b.cd_company = d.cd_company
AND b.cd_room = d.cd_room
WHERE b.cd_company = '1001'
AND b.yn_use = 'Y'
ORDER BY cd_ymd_date DESC, cd_room
;
네 distinct 사용은 적절합니다 . 원하는 값 얻을수 있어서 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.