一条sql实现年累计

显示全部楼层 · 2006-4-17 13:46:34

现在表中记录如下：
month
amout
1
10
2
15
3
12
要实现为
month
amout
年累计
1
10
10
2
15
25
3
12
37
即每月年累计为年初到本月的累计值
谢谢！

千问 · 2006-4-17 13:46:34

Oracle8i
SQL> create table test
2( month number(2) ,
3 amountnumber(5));
Table created.
SQL>
SQL>insert into test values ( 1 , 10) ;
1 row created.
SQL>insert into test values ( 2 , 15) ;
1 row created.
SQL>insert into test values ( 3 , 12) ;
1 row created.
SQL>commit;
Commit complete.
SQL> select month , amount , sum(amount) over (order by month) from test;
MONTH AMOUNT SUM(AMOUNT)OVER(ORDERBYMONTH)
---------- ---------- -----------------------------
1 10
10
2 15
25
3 12
37

千问 · 2006-4-17 13:46:34

OVER(ORDERBYMONTH)是什么意思？？？

千问 · 2006-4-17 13:46:34

谢谢2楼的，若是test表中还有品名字段，还按照品名分组的呢。
如：品名月份金额
1
1
10
2
1
12
1
2
13
2
2
9
1
3
11
2
3 8
要实现：
品名月份金额年累计
1
1
10
10
2
1
12
12
1
2
13
23
2
2
9
21
1
3
11
34
2
3 8
29

千问 · 2006-4-17 13:46:34

select month , amount , sum(amount) over (PARTITION BY 品名 order by month) from test;

千问 · 2006-4-17 13:46:34

非常感谢！

千问 · 2006-4-17 13:46:34

如果时间是几年的
month amout
200301 10
200302 15
200303 12
200401 10
200402 15
200403 12
又要按每年统计
200301 10 10
200302 15 25
200303 12 37
200401 10 10
200402 15 25
200403 12 37
怎么写呢

千问 · 2006-4-17 13:46:34

SELECT MON,VAL,
SUM(VAL)
OVER (PARTITION BY SUBSTR(MON,1,4)

ORDER BY MON)
FROM TEST4
ORDER BY MON,VAL