有表 order_sales 如下:
MONTH TOT_SALES
1 610697
2 428676
3 637031
4 541146
5 592935
6 501485
7 606914
8 460520
9 392898
10 510117
11 532889
12 492458
要求按照month升序,计算每月的累计值。
结果为:
1 610697 6307766
2 428676 6307766
3 637031 5697069
4 541146 5268393
5 592935 4631362
6 501485 4090216
7 606914 3497281
8 460520 2995796
9 392898 2388882
10 510117 1928362
11 532889 1535464
12 492458 1025347
MONTH TOT_SALES
1 610697
2 428676
3 637031
4 541146
5 592935
6 501485
7 606914
8 460520
9 392898
10 510117
11 532889
12 492458
要求按照month升序,计算每月的累计值。
结果为:
1 610697 6307766
2 428676 6307766
3 637031 5697069
4 541146 5268393
5 592935 4631362
6 501485 4090216
7 606914 3497281
8 460520 2995796
9 392898 2388882
10 510117 1928362
11 532889 1535464
12 492458 1025347
(select sum(TOT_SALES) from tab where MONTH<t.MONTH)
from tab t
select t.MONTH ,t.TOT_SALES,
(select sum(TOT_SALES) from tab where MONTH<=t.MONTH)
from tab t
[SYS@myoracle] SQL>WITH order_sales AS(
2 SELECT 1 MONTH ,610697 TOT_SALES FROM dual UNION ALL
3 SELECT 2 MONTH ,428676 TOT_SALES FROM dual UNION ALL
4 SELECT 3 MONTH ,637031 TOT_SALES FROM dual UNION ALL
5 SELECT 4 MONTH ,541146 TOT_SALES FROM dual UNION ALL
6 SELECT 5 MONTH ,592935 TOT_SALES FROM dual UNION ALL
7 SELECT 6 MONTH ,501485 TOT_SALES FROM dual UNION ALL
8 SELECT 7 MONTH ,606914 TOT_SALES FROM dual UNION ALL
9 SELECT 8 MONTH ,460520 TOT_SALES FROM dual UNION ALL
10 SELECT 9 MONTH ,392898 TOT_SALES FROM dual UNION ALL
11 SELECT 10 MONTH ,510117 TOT_SALES FROM dual UNION ALL
12 SELECT 11 MONTH ,532889 TOT_SALES FROM dual UNION ALL
13 SELECT 12 MONTH ,492458 TOT_SALES FROM dual
14 )SELECT MONTH,
15 TOT_SALES,
16 SUM(TOT_SALES) OVER(ORDER BY MONTH ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) SUM_
17 FROM ORDER_SALES
18 ; MONTH TOT_SALES SUM_
---------- ---------- ----------
1 610697 610697
2 428676 1039373
3 637031 1676404
4 541146 2217550
5 592935 2810485
6 501485 3311970
7 606914 3918884
8 460520 4379404
9 392898 4772302
10 510117 5282419
11 532889 5815308
12 492458 6307766已选择12行。
select n.month,
(select sum(t.TOT_SALES) from tab1 t where to_number(t.month) <= to_number(n.month)) as conscore
from tab1 nmonth 如果是VARCHAR2类型的要转化一下
或者像四楼所说