plsql逐行累加

plsql逐行累加

Select eba002,buydate,
2 buynum,outnum,nvl(Sum(buynum) over (partition by eba002 order by buydate),0)sumbuy,nvl(Sum(outnum)over (partition by eba002 order by buydate),0) sunout
3 From zhugyt1

[@more@]

plsql逐行累加

EBA002 BUYDATE BUYNUM OUTNUM
---------- ---------- ---------- -----------------
91001 2008-1-1 200
91001 2008-1-2 300 50
91001 2008-1-3 100
91002 2008-1-1 100
91002 2008-1-2 20
91002 2008-1-3 50

需要依據eba002計算出總的購入數與銷出數,結果如下:

EBA002 BUYDATE BUYNUM OUTNUM SUMBUY SUNOUT
---------- ---------- ---------- ----------------- ---------- ----------
91001 2008-1-1 200 200 0
91001 2008-1-2 300 50 500 50
91001 2008-1-3 100 500 150
91002 2008-1-1 100 100 0
91002 2008-1-2 20 120 0
91002 2008-1-3 50 120 50

一下子感覺竟是似曾相識,卻想不起來,原來可以用

Select eba002,buydate,
2 buynum,outnum,nvl(Sum(buynum) over (partition by eba002 order by buydate),0)sumbuy,nvl(Sum(outnum)over (partition by eba002 order by buydate),0) sunout
3 From zhugyt1

解決.

對sum() over(partition by...)看來還得多熟悉.不知sqlserver類似問題又是如何解決的.