Oracle查询过去一个时间点的库存

65 阅读 0 评论 43 点赞

我是靠谱客的博主义气月亮，最近开发中收集的这篇文章主要介绍Oracle查询过去一个时间点的库存，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

create table CUX_ONHAND
(
item_code
VARCHAR2(30),
subinventory
VARCHAR2(30),
transaction_qty NUMBER
);
create table CUX_TRANSACTION
(
item_code
VARCHAR2(30),
subinventory
VARCHAR2(30),
transaction_date DATE,
transaction_qty
NUMBER
);

1.计算2016年10月1日0点0分0秒现有量，数量为0的不显示，显示字段：物料、仓库、数量
2.计算不同仓库的现有量，显示字段：物料、仓库A数量、仓库B数量、其他仓库数量
1

SELECT tt.item_code,
       tt.subinventory,
       SUM(tt.transaction_qty) transaction_qty
  FROM (SELECT o.item_code, o.subinventory, o.transaction_qty
          FROM cux_onhand o
        UNION ALL
        SELECT t.item_code,
               t.subinventory,
               transaction_qty * (-1) transaction_qty
          FROM cux_transaction t
         WHERE t.transaction_date >=
               to_date('2016-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tt
 GROUP BY tt.item_code, tt.subinventory
HAVING SUM(tt.transaction_qty) > 0

SELECT item_code,
SUM(a.a_qty),
SUM(a.b_qty),
SUM(a.o_qty)
FROM (SELECT t.item_code,
decode(t.subinventory,
'A',
t.transaction_qty,
0) a_qty,
decode(t.subinventory,
'B',
t.transaction_qty,
0) b_qty,
decode(t.subinventory,
'A',
0,
'B',
0,
t.transaction_qty) o_qty
FROM cux_onhand t) a
GROUP BY a.item_code