表INV是目前的库存数
GOODS_CD WAREHOUSE INV_QTY
A CK1 1000
A CK2 2000
A CK3 1500
B CK3 1200
B CK5 1700
C CK4 1100表PLAN是在计划PLAN_DATE生产了PLAN_QTY个物品到WAREHOUSE中
GOODS_CD PLAN_DATE WAREHOUSE PLAN_QTY
A 20121001 CK1 1000
A 20121002 CK2 2360
A 20121001 CK3 1400
B 20121102 CK2 500
C 20121020 CK3 800
C 20121026 CK5 800表DISB是DISB_DATE从WAREHOUSE中转出DISB_QTY个物品
GOODS DISB_DATE WAREHOUSE DISB_QTY
A 20121003 CK2 600
A 20121004 CK1 200
B 20121010 CK5 300
C 20121017 CK3 400
C 20121030 CK4 100想得到的结果是在20121020,每一个GOODS_CD在每个仓库的库存数 = 仓库目前库存数 + 20121020前的PLAN_QTY - 20121020前的DISB_QTY例如,结果应该是:
GOODS WAREHOUSE QTY
A CK1 1800
A CK2 3760
A CK3 1500
B CK2 500
B CK3 1200
B CK5 1400
C CK4 1100
C CK3 400
SELECT 'A' AS GOODS_CD,'CK1' AS WAREHOUSE,'1000' AS INV_QTY FROM DUAL
UNION
SELECT 'A' AS GOODS_CD,'CK2' AS WAREHOUSE,'2000' AS INV_QTY FROM DUAL
UNION
SELECT 'A' AS GOODS_CD,'CK3' AS WAREHOUSE,'1500' AS INV_QTY FROM DUAL
UNION
SELECT 'B' AS GOODS_CD,'CK3' AS WAREHOUSE,'1200' AS INV_QTY FROM DUAL
UNION
SELECT 'B' AS GOODS_CD,'CK5' AS WAREHOUSE,'1700' AS INV_QTY FROM DUAL
UNION
SELECT 'C' AS GOODS_CD,'CK4' AS WAREHOUSE,'1100' AS INV_QTY FROM DUAL
)
,
PLAN AS (
SELECT 'A' AS GOODS_CD,'20121001' AS PLAN_DATE,'CK1' AS WAREHOUSE,'1000' AS PLAN_QTY FROM DUAL
UNION
SELECT 'A' AS GOODS_CD,'20121002' AS PLAN_DATE,'CK2' AS WAREHOUSE,'2360' AS PLAN_QTY FROM DUAL
UNION
SELECT 'A' AS GOODS_CD,'20121001' AS PLAN_DATE,'CK3' AS WAREHOUSE,'1400' AS PLAN_QTY FROM DUAL
UNION
SELECT 'B' AS GOODS_CD,'20121102' AS PLAN_DATE,'CK2' AS WAREHOUSE,'500' AS PLAN_QTY FROM DUAL
UNION
SELECT 'C' AS GOODS_CD,'20121020' AS PLAN_DATE,'CK3' AS WAREHOUSE,'800' AS PLAN_QTY FROM DUAL
UNION
SELECT 'C' AS GOODS_CD,'20121026' AS PLAN_DATE,'CK5' AS WAREHOUSE,'800' AS PLAN_QTY FROM DUAL
)
,
DISB AS (
SELECT 'A' AS GOODS_CD,'20121003' AS DISB_DATE, 'CK2' AS WAREHOUSE,'600' AS DISB_QTY FROM DUAL
UNION
SELECT 'A' AS GOODS_CD,'20121004' AS DISB_DATE, 'CK1' AS WAREHOUSE,'200' AS DISB_QTY FROM DUAL
UNION
SELECT 'B' AS GOODS_CD,'20121010' AS DISB_DATE, 'CK5' AS WAREHOUSE,'300' AS DISB_QTY FROM DUAL
UNION
SELECT 'C' AS GOODS_CD,'20121017' AS DISB_DATE, 'CK3' AS WAREHOUSE,'400' AS DISB_QTY FROM DUAL
UNION
SELECT 'C' AS GOODS_CD,'20121030' AS DISB_DATE, 'CK4' AS WAREHOUSE,'100' AS DISB_QTY FROM DUAL
)SELECT NVL(T.GOODS_CD,T3.GOODS_CD) AS GOODS_CD,
NVL(T.WAREHOUSE,T3.WAREHOUSE) AS WAREHOUSE,
T.INQTY-NVL(T3.DISB_QTY,0) AS QTY
FROM (
SELECT NVL(GOODS_CD_T1,GOODS_CD_T2) AS GOODS_CD,
NVL(WAREHOUSE_T1,WAREHOUSE_T2) AS WAREHOUSE,
NVL(INV_QTY_T1,0)+NVL(PLAN_QTY_T2,0) AS INQTY
FROM(
SELECT T1.GOODS_CD AS GOODS_CD_T1,
T1.WAREHOUSE AS WAREHOUSE_T1,
T1.INV_QTY AS INV_QTY_T1,
T2.GOODS_CD AS GOODS_CD_T2,
T2.WAREHOUSE AS WAREHOUSE_T2,
T2.PLAN_QTY AS PLAN_QTY_T2
FROM INV T1
FULL JOIN PLAN T2 ON T1.GOODS_CD = T2.GOODS_CD
AND T1.WAREHOUSE = T2.WAREHOUSE
WHERE (T2.PLAN_DATE <= '20121020' OR T2.PLAN_DATE IS NULL))) T
LEFT JOIN DISB T3 ON T.GOODS_CD = T3.GOODS_CD
AND T.WAREHOUSE = T3.WAREHOUSE
AND (T3.DISB_DATE <= '20121020' OR T3.DISB_DATE IS NULL)
ORDER BY GOODS_CD,WAREHOUSE