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类似问题又是如何解决的.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11419868/viewspace-1008033/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11419868/viewspace-1008033/