데이터베이스 질문과 답변
수불쿼리 좀 도와 주세요..
0
5
1,987
by 시리님이시네 [2012.07.18 15:25:49]
만약 5월에 입고가 3업체에서 각각 2번씩 입고가.되었다는 가정하에
a 업체 7월 1일에 100 15일에 100
b 업체 3일에 100 10일에 100
c 업체 2일에 100 9 일에 100 입고
쿼리 실행결과
a 업체
날짜 입고 출고 잔여
1 100 0 100
2 0 10 90
3 0 0 90
4 0 0 90
5 0 80 10 <---하루에 여러건이 나갈수도 있습니다.
6 0 0 10
.
.
.
.
b업체
날짜 입고 출고 잔여
1 0 0 0
2 0 0 0
3 100 0 100
.
.
.
c업체 동일
입고나 출고가.일어나지.않은.날에도 잔여량.표시가 되어야.합니다
테이블 날짜 테이블 2020년까지의 날짜 테이블.
입고 테이블 [거래처 코드,거래처명 ,입고량,제품코드,제품명날짜]
출고 테이블 [거래처 코드,거래처명 ,출고량,제품코드,제품명날짜]
기초(이월 수량) 데이터 테이블
잘 안풀려서 문의드립니다 도움좀 주세요.ㅠㅠ
by 마농
[2012.07.19 14:23:42]
WITH t_in AS
(-- 입고
SELECT 'a' co, '20120701' dt, 100 amt FROM dual
UNION ALL SELECT 'a', '20120715', 100 FROM dual
UNION ALL SELECT 'b', '20120703', 100 FROM dual
UNION ALL SELECT 'b', '20120710', 100 FROM dual
UNION ALL SELECT 'c', '20120702', 100 FROM dual
UNION ALL SELECT 'c', '20120709', 100 FROM dual
)
, t_out AS
(-- 출고
SELECT 'a' co, '20120702' dt, 10 amt FROM dual
UNION ALL SELECT 'a', '20120705', 30 FROM dual
UNION ALL SELECT 'a', '20120705', 50 FROM dual
)
, t_bas AS
(-- 이월
SELECT 'c' co, '20120701' dt, 40 amt FROM dual
)
, t_cal AS
(-- 달력
SELECT TO_CHAR(TO_DATE('201207', 'yyyymm') + LEVEL - 1, 'yyyymmdd') dt
FROM dual
CONNECT BY LEVEL <= 31
)
SELECT b.co 회사
, a.dt 일자
, NVL(amt_bas, 0) 이월
, NVL(amt_in , 0) 입고
, NVL(amt_out, 0) 출고
, SUM(NVL(amt_bas, 0) + NVL(amt_in , 0) - NVL(amt_out, 0))
OVER(PARTITION BY b.co ORDER BY a.dt) 재고
FROM t_cal a
LEFT OUTER JOIN
(
SELECT co, dt
, SUM(amt_bas) amt_bas
, SUM(amt_in ) amt_in
, SUM(amt_out) amt_out
FROM (
SELECT co, dt, amt amt_bas, 0 amt_in, 0 amt_out FROM t_bas
UNION ALL
SELECT co, dt, 0 amt_bas, amt amt_in, 0 amt_out FROM t_in
UNION ALL
SELECT co, dt, 0 amt_bas, 0 amt_in, amt amt_out FROM t_out
)
WHERE dt LIKE '201207'||'%'
GROUP BY co, dt
) b
PARTITION BY (b.co)
ON a.dt = b.dt
WHERE a.dt LIKE '201207'||'%'
;
by 시리님이시네
[2012.07.24 19:18:07]
마농님 정말 감사 합니다.. PARTITION BY (b.co) 이쪽 부분이 잘 이해가 안가서요 실직적으로 실행 해도 저쪽에서 에러 걸리는것 같은데..덕분에 처음 본;;
WITH t_in AS 절 잘 배웠습니다.. by 시리님이시네
[2012.07.24 19:20:27]
아 그리고
SELECT TO_CHAR(TO_DATE('201207', 'yyyymm') + LEVEL - 1, 'yyyymmdd') dt FROM dual CONNECT BY LEVEL <= 31 이부분도 인상 깊었습니다..낭짜 테이블을 일일이 만들었는데..저렇게 좋은 방법이 있었다는게..
by 마농
[2012.07.24 20:21:28]
-- 음.. Partition Outer Join 은 10g 의 새 기능입니다.
-- 9i 라면 쿼리가 좀더 복잡합니다.
WITH t_in AS
(-- 입고
SELECT 'a' co, '20120701' dt, 100 amt FROM dual
UNION ALL SELECT 'a', '20120715', 100 FROM dual
UNION ALL SELECT 'b', '20120703', 100 FROM dual
UNION ALL SELECT 'b', '20120710', 100 FROM dual
UNION ALL SELECT 'c', '20120702', 100 FROM dual
UNION ALL SELECT 'c', '20120709', 100 FROM dual
)
, t_out AS
(-- 출고
SELECT 'a' co, '20120702' dt, 10 amt FROM dual
UNION ALL SELECT 'a', '20120705', 30 FROM dual
UNION ALL SELECT 'a', '20120705', 50 FROM dual
)
, t_bas AS
(-- 이월
SELECT 'c' co, '20120701' dt, 40 amt FROM dual
)
, t_cal AS
(-- 달력
SELECT TO_CHAR(TO_DATE('201207', 'yyyymm') + LEVEL - 1, 'yyyymmdd') dt
FROM dual
CONNECT BY LEVEL <= 31
)
, tmp_mst AS
(
SELECT co, dt
, SUM(amt_bas) amt_bas
, SUM(amt_in ) amt_in
, SUM(amt_out) amt_out
FROM (
SELECT co, dt, amt amt_bas, 0 amt_in, 0 amt_out FROM t_bas
UNION ALL
SELECT co, dt, 0 amt_bas, amt amt_in, 0 amt_out FROM t_in
UNION ALL
SELECT co, dt, 0 amt_bas, 0 amt_in, amt amt_out FROM t_out
)
WHERE dt LIKE '201207'||'%'
GROUP BY co, dt
)
, tmp_cal AS
(
SELECT co, dt
FROM (SELECT DISTINCT co FROM tmp_mst)
, (SELECT dt FROM t_cal WHERE dt LIKE '201207'||'%')
)
SELECT a.co 회사
, a.dt 일자
, NVL(amt_bas, 0) 이월
, NVL(amt_in , 0) 입고
, NVL(amt_out, 0) 출고
, SUM(NVL(amt_bas, 0) + NVL(amt_in , 0) - NVL(amt_out, 0))
OVER(PARTITION BY a.co ORDER BY a.dt) 재고
FROM tmp_cal a
, tmp_mst b
WHERE a.co = b.co(+)
AND a.dt = b.dt(+)
;
by 시리님이시네
[2012.07.26 16:13:19]
깔끔한쿼리 정말 감사 합니다.... 이 쿼리 하나로 너무 많은걸 배운것 같아 흐믓 합니다 더운날 더위 조심 하세요 감사 합니다..
) 버튼을 클릭하여 작성 하시면 됩니다.