流水帳表結構如下：
品名 發生日期 規格 入出庫標志 數量
地磚 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
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
我提供的資料只是代表性，如果這幾條我自己手工計算就好了。如果按照你的方式，10萬條該如何處理？uj5u.com熱心網友回復：
？？？我寫的你啥疑問 10w條怎么了uj5u.com熱心網友回復：
樓上的沒問題，前面一段是建立虛擬表，你就用下面這個稍作修改就好了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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/19538.html
標籤：高級技術