关闭

sum over用法

标签: sqlcmdtable
1896人阅读 评论(0) 收藏 举报
分类:

问题:
TABLE如下
日期 收入 支出
2000/3/1 50 30
2000/3/2 45 60
2000/3/5 60 10
2000/3/5 60 10

显示结果如下:
日期 收入 支出 余額
2000/3/1 50 30 20
2000/3/2 45 60 5
2000/3/3 0 0 5
2000/3/4 0 0 5
2000/3/5 120 20 105
用sql语句实现

SQL> select * from cmd;

COLA                      COLB       COLC                                                          
------------------- ---------- ----------                                                          
01/09/2007 00:00:00         50         30                                                          
02/09/2007 00:00:00         45         60                                                          
03/09/2007 00:00:00         60         10                                                          
05/09/2007 00:00:00         60         10                                                          
05/09/2007 00:00:00         60         10                                                          
05/09/2007 00:00:00         60         50                                                          

已选择6行。

SQL> select cola 日期,colb 收入,colc 支出,
  2  sum(colb) over (order by cola rows between unbounded preceding and current row)
  3  -sum(colc) over (order by cola rows between unbounded preceding and current row) 余额
  4  from cmd;

日期                      收入       支出       余额                                               
------------------- ---------- ---------- ----------                                               
01/09/2007 00:00:00         50         30         20                                               
02/09/2007 00:00:00         45         60          5                                               
03/09/2007 00:00:00         60         10         55                                               
05/09/2007 00:00:00         60         10        105                                               
05/09/2007 00:00:00         60         10        155                                               
05/09/2007 00:00:00         60         50        165                                               

已选择6行。

SQL> select cola 日期,colb 收入,colc 支出,
  2  sum(colb) over (order by cola range unbounded preceding)
  3  -sum(colc) over (order by cola range unbounded preceding) 余额
  4  from cmd;

日期                      收入       支出       余额                                               
------------------- ---------- ---------- ----------                                               
01/09/2007 00:00:00         50         30         20                                               
02/09/2007 00:00:00         45         60          5                                               
03/09/2007 00:00:00         60         10         55                                               
05/09/2007 00:00:00         60         10        165                                               
05/09/2007 00:00:00         60         10        165                                               
05/09/2007 00:00:00         60         50        165                                               

已选择6行。

SQL> spool off

还有其它参数用法,只有在这里不适用如下:
select cola 日期,colb 收入,colc 支出,
sum(colb) over (partition by cola order by cola range unbounded preceding)
-sum(colc) over (partition by cola order by cola range unbounded preceding) 余额
from cmd;

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:216586次
    • 积分:3520
    • 等级:
    • 排名:第10017名
    • 原创:140篇
    • 转载:8篇
    • 译文:0篇
    • 评论:14条
    JAVA JSP