求sql。按月累计

[dws2004]

有表 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

[iqlife]

sum()over()

[BenChiM888]

顶。

over(rows between preceding unlimited and current row)
[Quote=引用 1 楼 java3344520 的回复:]
sum()over()
[/Quote]

[dws2004]

额 结果表复制错了，正确的如下：
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

楼上的大哥能写的详细点吗？

[oO寒枫Oo]

select t.MONTH ,t.TOT_SALES,
(select sum(TOT_SALES) from tab where MONTH<t.MONTH)
from tab t

[oO寒枫Oo]

select t.MONTH ,t.TOT_SALES,
(select sum(TOT_SALES) from tab where MONTH<=t.MONTH)
from tab t

[BenChiM888]

[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行。

[programmerxiaocai]

select  n.month,
       (select sum(t.TOT_SALES) from tab1 t where to_number(t.month) <= to_number(n.month)) as conscore
  from tab1 n

month 如果是VARCHAR2类型的要转化一下

[wayne很忙]

可以select month,tot_sales,sum(tot_sales) over(order by month asc) sum from order_sales；
或者像四楼所说