帮助求期初期末库存的SQL语句

[yanhaiwang]

流水帐表结构如下：
品名 发生日期 规格 入出库标志 数量
地砖 2008-01-08 10*10 1 100
地砖 2008-01-08 10*10 1 100
地砖 2008-02-06 10*10 1 30
地砖 2008-02-06 10*10 -1 60
地砖 2008-04-01 10*10 1 50
地砖 2008-05-13 10*10 -1 60
地砖 2008-02-21 10*10 -1 20
地砖 2008-03-25 10*10 -1 50
地砖 2008-05-31 10*10 -1 70

样式表结构如下：
名称 日期 规格 期初 出库 入库 期末
地砖 2008-01 10*10 0 0 200 200
地砖 2008-02 10*10 200 60 30 150
地砖 2008-03 10*10 150 50 0 100
地砖 2008-04 10*10 100 0 50 150
地砖 2008-05 10*10 150 130 0 20

[万匹羊驼]

with ta as (select '地砖' as names,'2008-01-08' days,'10*10' guig,1 inout,100 vol from dual union all select '地砖','2008-01-08','10*10',1,100 from dual union all select '地砖','2008-02-06','10*10',1,30 from dual union all select '地砖','2008-02-06','10*10',-1,60 from dual union all select '地砖','2008-04-01','10*10',1,50 from dual union all select '地砖','2008-05-13','10*10',-1,60 from dual union all select '地砖','2008-02-21','10*10',-1,20 from dual union all select '地砖','2008-03-25','10*10',-1,50 from dual union all select '地砖','2008-05-31','10*10',-1,70 from dual ), tb as( select names,substr(days,1,7) as mon,guig,nvl(sum(decode(inout,-1,vol)),0)as out_vol,nvl(sum(decode(inout,1,vol)),0) in_vol from ta group by names,substr(days,1,7),guig order by names,mon) select names,mon,guig,out_vol,in_vol,sum(in_vol-out_vol)over(order by names,guig,mon) from tb

[yanhaiwang]

我提供的数据只是代表性，如果这几条我自己手工计算就好了。如果按照你的方式，10万条该如何处理？

[万匹羊驼]

？？？我写的你啥疑问 10w条怎么了

[Ayue(๑•ั็ω•็ั๑)]

楼上的没问题，前面一段是建立虚拟表，你就用下面这个稍作修改就好了 with tb as( select names,substr(days,1,7) as mon,guig,nvl(sum(decode(inout,-1,vol)),0)as out_vol,nvl(sum(decode(inout,1,vol)),0) in_vol from ta group by names,substr(days,1,7),guig order by names,mon) select names,mon,guig,out_vol,in_vol,sum(in_vol-out_vol)over(order by names,guig,mon) from tb