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

Question

流水帐表结构如下：
品名            发生日期               规格         入出库标志    数量
地砖          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

Answer 1

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