데이터베이스 질문과 답변
a 기준날짜 테이블(선택한 from~to 날짜 모두 조회드되도록 만든 임시 테이블)
b 입고 테이블
c 출고 테이블
Select a.기준날짜,
b.입고량, b.입고업체정보,
c.출고량, c.출고업체정보
from a
left join b
on a.기준날짜 =b.입고날짜
left join c
on a.기준날짜 = c. 출고날짜
로하니 입고, 출고 행에 따라 데이터가 중복되어 조회되어요. 사용자가 선택한 날짜는 입고, 출고에 상관없이 모두 조회되고, 입고, 출고 정보를 조회하고 싶은데 중복이 되네요. 도와주세요.
중복이 어떤 건지 보여주세요. (현재 결과표 예시)
어떻게 보고 싶은지 보여주세요. (원하는 결과표)
WITH TEMP_DATE AS
(
--사용자 입력 from ~ to
SELECT '2022-03-01' AS START_DATE, '2022-03-31' AS END_DATE FROM DUAL
)
, SDT_DATE AS (
SELECT TO_CHAR(SDT + LV -1, 'YYYY.MM.DD') DT, TO_CHAR(SDT + LV -1, 'MM.DD') DT1
FROM (SELECT TO_DATE(START_DATE, 'YYYY-MM-DD') AS SDT, TO_DATE(END_DATE, 'YYYY-MM-DD') AS EDT FROM TEMP_DATE)
, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <=365)
WHERE LV <= EDT -SDT +1
ORDER BY DT
)
--입고 임시 테이블
,IN_TEMP_WGT AS (
SELECT '2022.03.01' AS STD_DT, '10' AS WGT1, '입고회사1' AS DATA1 FROM DUAL UNION ALL
SELECT '2022.03.01' AS STD_DT, '20' AS WGT1, '입고회사1' AS DATA1 FROM DUAL UNION ALL
SELECT '2022.03.02' AS STD_DT, '10' AS WGT1, '입고회사2' AS DATA1 FROM DUAL UNION ALL
SELECT '2022.03.03' AS STD_DT, '10' AS WGT1, '입고회사1' AS DATA1 FROM DUAL UNION ALL
SELECT '2022.03.03' AS STD_DT, '20' AS WGT1, '입고회사2' AS DATA1 FROM DUAL
)
--입고 테이블, 날짜, 입고회사정보로 중량합
,IN_WGT AS (
SELECT STD_DT, SUM(WGT1) AS WGT1, DATA1
FROM IN_TEMP_WGT
WHERE STD_DT BETWEEN '2022.03.01' AND '2022.03.31'
GROUP BY STD_DT, DATA1
)
--출고 임시 테이블
,OUT_TEMP_WGT AS (
SELECT '2022.03.01' AS STD_DT, '10' AS WGT2, '출고회사1' AS DATA2 FROM DUAL UNION ALL
SELECT '2022.03.02' AS STD_DT, '20' AS WGT2, '출고회사1' AS DATA2 FROM DUAL UNION ALL
SELECT '2022.03.02' AS STD_DT, '10' AS WGT2, '출고회사2' AS DATA2 FROM DUAL UNION ALL
SELECT '2022.03.03' AS STD_DT, '10' AS WGT2, '출고회사1' AS DATA2 FROM DUAL UNION ALL
SELECT '2022.03.04' AS STD_DT, '20' AS WGT2, '출고회사2' AS DATA2 FROM DUAL
)
--출고 테이블, 날짜, 출고회사정보로 중량합
,OUT_WGT AS (
SELECT STD_DT, SUM(WGT2) AS WGT2, DATA2
FROM OUT_TEMP_WGT
WHERE STD_DT BETWEEN '2022.03.01' AND '2022.03.31'
GROUP BY STD_DT, DATA2
)
--SELECT * FROM IN_WGT;
SELECT
A.DT1, A.DT
,B.WGT1
,B.DATA1
,C.WGT2
,C.DATA2
FROM SDT_DATE A
LEFT JOIN IN_WGT B
ON A.DT = B.STD_DT
LEFT JOIN OUT_WGT C
ON A.DT = C.STD_DT
WITH TEMP_DATE AS
(
--사용자 입력 from ~ to
SELECT '2022-03-01' AS START_DATE, '2022-03-31' AS END_DATE FROM DUAL
)
, SDT_DATE AS (
SELECT TO_CHAR(SDT + LV -1, 'YYYY.MM.DD') DT, TO_CHAR(SDT + LV -1, 'MM.DD') DT1
FROM (SELECT TO_DATE(START_DATE, 'YYYY-MM-DD') AS SDT, TO_DATE(END_DATE, 'YYYY-MM-DD') AS EDT FROM TEMP_DATE)
, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <=365)
WHERE LV <= EDT -SDT +1
ORDER BY DT
)
--입고 임시 테이블
,IN_TEMP_WGT AS (
SELECT '2022.03.01' AS STD_DT, '10' AS WGT1, '입고회사1' AS DATA1 FROM DUAL UNION ALL
SELECT '2022.03.01' AS STD_DT, '20' AS WGT1, '입고회사1' AS DATA1 FROM DUAL UNION ALL
SELECT '2022.03.02' AS STD_DT, '10' AS WGT1, '입고회사2' AS DATA1 FROM DUAL UNION ALL
SELECT '2022.03.03' AS STD_DT, '10' AS WGT1, '입고회사1' AS DATA1 FROM DUAL UNION ALL
SELECT '2022.03.03' AS STD_DT, '20' AS WGT1, '입고회사2' AS DATA1 FROM DUAL
)
--입고 테이블, 날짜, 입괴회사정보로 중량합
,IN_WGT AS (
SELECT STD_DT, SUM(WGT1) AS WGT1, DATA1
FROM IN_TEMP_WGT
WHERE STD_DT BETWEEN '2022.03.01' AND '2022.03.31'
GROUP BY STD_DT, DATA1
)
--입고 임시 테이블
,OUT_TEMP_WGT AS (
SELECT '2022.03.01' AS STD_DT, '10' AS WGT2, '출고회사1' AS DATA2 FROM DUAL UNION ALL
SELECT '2022.03.02' AS STD_DT, '20' AS WGT2, '출고회사1' AS DATA2 FROM DUAL UNION ALL
SELECT '2022.03.02' AS STD_DT, '10' AS WGT2, '출고회사2' AS DATA2 FROM DUAL UNION ALL
SELECT '2022.03.03' AS STD_DT, '10' AS WGT2, '출고회사1' AS DATA2 FROM DUAL UNION ALL
SELECT '2022.03.04' AS STD_DT, '20' AS WGT2, '출고회사2' AS DATA2 FROM DUAL
)
--입고 테이블, 날짜, 입괴회사정보로 중량합
,OUT_WGT AS (
SELECT STD_DT, SUM(WGT2) AS WGT2, DATA2
FROM OUT_TEMP_WGT
WHERE STD_DT BETWEEN '2022.03.01' AND '2022.03.31'
GROUP BY STD_DT, DATA2
)
SELECT
A.DT
,B.WGT1
,B.DATA1
,C.WGT2
,C.DATA2
FROM SDT_DATE A
LEFT JOIN IN_WGT B
ON A.DT = B.STD_DT
LEFT JOIN OUT_WGT C
ON A.DT = C.STD_DT
| 2022.03.01 | 30 | 입고회사1 | 10 | 출고회사1 |
| 2022.03.02 | 10 | 입고회사2 | 20 | 출고회사1 |
| 2022.03.02 | 10 | 입고회사2 | 10 | 출고회사2 |
| 2022.03.03 | 20 | 입고회사2 | 10 | 출고회사1 |
| 2022.03.03 | 10 | 입고회사1 | 10 | 출고회사1 |
| 2022.03.04 | 20 | 출고회사2 |
위의 쿼리로 작성하면 이렇게 나오는 결과를
| 2022.03.01 | 30 | 입고회사1 | 10 | 출고회사1 |
| 2022.03.02 | 10 | 입고회사2 | 20 | 출고회사1 |
| 2022.03.02 | 10 | 출고회사2 | ||
| 2022.03.03 | 20 | 입고회사2 | 10 | 출고회사1 |
| 2022.03.03 | 10 | 입고회사1 | ||
| 2022.03.04 | 20 | 출고회사2 | ||
이렇게 나오도록 하고 싶습니다.
참고로, 입고 , 출고 테이블은 동일 테이블입니다. 쿼리로는 안되는 것 같아서 소스로 돌려서 같은 값이 나오면 공백처리를 해야하나 생각하고 있어요. 쿼리로 해결 될 수 있었으면 좋겠네요. 부탁드립니다.
중간 결과물이 아닌 원본을 가지고 질문해 주시는게 더 좋습니다.
지금 올려주신 중간 결과물을 가지고 최종 결과를 뽑으면 쿼리만 더 복잡해 집니다.
하나의 테이블에 입출고 구분이 있다고 가정하고 답변 드립니다.
WITH wgt_t AS
(
SELECT 'I' gb, '2022.03.01' std_dt, 10 wgt, '입고회사1' com FROM dual
UNION ALL SELECT 'I', '2022.03.01', 20, '입고회사1' FROM dual
UNION ALL SELECT 'I', '2022.03.02', 10, '입고회사2' FROM dual
UNION ALL SELECT 'I', '2022.03.03', 10, '입고회사1' FROM dual
UNION ALL SELECT 'I', '2022.03.03', 20, '입고회사2' FROM dual
UNION ALL SELECT 'O', '2022.03.01', 10, '출고회사1' FROM dual
UNION ALL SELECT 'O', '2022.03.02', 20, '출고회사1' FROM dual
UNION ALL SELECT 'O', '2022.03.02', 10, '출고회사2' FROM dual
UNION ALL SELECT 'O', '2022.03.03', 10, '출고회사1' FROM dual
UNION ALL SELECT 'O', '2022.03.04', 20, '출고회사2' FROM dual
)
SELECT a.dt
, b.wgt1
, b.com1
, b.wgt2
, b.com2
FROM (SELECT TO_CHAR(sdt + LEVEL - 1, 'yyyy.mm.dd') dt
FROM (SELECT TO_DATE('2022.03.01', 'yyyy.mm.dd') sdt
, TO_DATE('2022.03.31', 'yyyy.mm.dd') edt
FROM dual)
CONNECT BY LEVEL <= edt - sdt + 1
) a
LEFT OUTER JOIN
(SELECT std_dt, rn
, MIN(DECODE(gb, 'I', wgt)) wgt1
, MIN(DECODE(gb, 'I', com)) com1
, MIN(DECODE(gb, 'O', wgt)) wgt2
, MIN(DECODE(gb, 'O', com)) com2
FROM (SELECT gb, std_dt, com
, SUM(wgt) wgt
, ROW_NUMBER() OVER(PARTITION BY gb, std_dt ORDER BY com) rn
FROM wgt_t
WHERE std_dt BETWEEN '2022.03.01' AND '2022.03.31'
GROUP BY gb, std_dt, com
)
GROUP BY std_dt, rn
) b
ON a.dt = b.std_dt
ORDER BY dt, rn
;
감사합니다. 덕분에 잘 해결되었어요.
) 버튼을 클릭하여 작성 하시면 됩니다.