实现如下功能:A物料期初库存:0Table:
物料 交易日期 交易数量 交易类型
A 2009-10-01 10 收料
A 2009-10-01 5 收料
A 2009-10-05 6 领料
A 2009-11-01 8 领料实现如下查询结果:
物料 交易日期 交易数量 结存数量
A 2009-10-01 10 10
A 2009-10-01 5 15
A 2009-10-05 6 9
A 2009-10-05 8 1请教高手如何实现,谢谢咯
物料 交易日期 交易数量 交易类型
A 2009-10-01 10 收料
A 2009-10-01 5 收料
A 2009-10-05 6 领料
A 2009-11-01 8 领料实现如下查询结果:
物料 交易日期 交易数量 结存数量
A 2009-10-01 10 10
A 2009-10-01 5 15
A 2009-10-05 6 9
A 2009-10-05 8 1请教高手如何实现,谢谢咯
---- ---------- ---------- --------
A 2009-10-01 10 收料
A 2009-10-01 5 收料
A 2009-10-05 6 领料
A 2009-10-05 8 领料已用时间: 00: 00: 00.01
14:24:59 tina@PRACTICE> select 物料,交易日期,交易数量,sum(decode(交易类型,'收料',交易数量,'领料',-交易数量,0)) over(partition by 物料 order by rownum) 结存数量 from tb;物料 交易日期 交易数量 结存数量
---- ---------- ---------- ----------
A 2009-10-01 10 10
A 2009-10-01 5 15
A 2009-10-05 6 9
A 2009-10-05 8 1已用时间: 00: 00: 00.01
(SELECT 'A' AS good, DATE '2009-10-1' AS date1, 10 AS qty, 1 AS flag
FROM dual
UNION ALL
SELECT 'A' AS good, DATE '2009-10-1' AS date1, 5 AS qty, 1 AS flag
FROM dual
UNION ALL
SELECT 'A' AS good, DATE '2009-10-1' AS date1, 6 AS qty, 0 AS flag
FROM dual
UNION ALL
SELECT 'A' AS good, DATE '2009-10-1' AS date1, 8 AS qty, 0 AS flag
FROM dual)
SELECT SUM (qty) over (PARTITION BY good ORDER BY rn rows BETWEEN unbounded preceding AND 0 preceding)
FROM (SELECT good,
date1,
decode(flag, 1, qty, qty * -1) AS qty,
flag,
rownum rn
FROM t) a