很感谢昨下午各位高手指点，那个汇总SQL的要求有些变化，那个帖子已经给分。

Question

有个test表 
day(日期)  class(类别) price（价格） 
2008-9-1      1          50 
2008-9-1      2          40 
2008-9-2      1          30 
2008-9-2      2          20 需要按日期对price汇总求和，并且需要对该月内截止到当前日期，所有类别的价格求和， 要求最后显示的结果是： 
              日期求和    本月内截止到当前天所有值求和      
2008-9-1      90          90   －－1号所有记录求和
2008-9-2      50          140  －－1号和2号所有记录求和请教这个怎么写， 

Answer 1

SELECT DAY, SUM(PRICE), S
  FROM (SELECT S.*,
               SUM(PRICE) OVER( ORDER BY DAY RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) S
          FROM TESTX S)
 GROUP BY DAY, S
 ORDER BY 1;
输出:
DAY SUM(PRICE) S
2008-9-1 90 90
2008-9-2 50 140

Answer 2

WITH TEST AS 
    (SELECT to_date('2008-9-1','yyyy-mm-dd') day, 1 class,50 price 
        FROM DUAL 
      UNION ALL 
     SELECT to_date('2008-9-1','yyyy-mm-dd') day, 2 class,40 price 
        FROM DUAL 
      UNION ALL 
     SELECT to_date('2008-9-2','yyyy-mm-dd') day, 1 class,30 price 
        FROM DUAL 
      UNION ALL 
     SELECT to_date('2008-9-2','yyyy-mm-dd') day, 2 class,20 price 
        FROM DUAL 
     ) 
select day,
   sum(price) 日期求和,
   jj 本月内所有值求和 
from
(       
    select t.*,
       sum(price) over (order by to_char(day,'yyyy-mm-dd')) jj
    from test t
)
group by day,jj;

Answer 3

跟昨天我写的比较一下，partition by 和 order by 的区别

Answer 4

select distinct t.day,
                sum(t.price) OVER(PARTITION BY t.day),
                sum(t.price) OVER(PARTITION BY to_char(day, 'yyyy-mm') ORDER BY t.day)
  from test t

Answer 5

SQL> select * from t;DAY                                                              CLASS                                   PRICE
------------------------------ --------------------------------------- ---------------------------------------
2008-9-1                                                             1                                      50
2008-9-1                                                             2                                      40
2008-9-2                                                             1                                      30
2008-9-2                                                             2                                      20SQL> 
SQL> select day,sum1,lag(sum1,1,0)over(order by day)+sum1 as sum2 from (
  2    select day,sum(price) sum1 from t group by day
  3  );DAY                                  SUM1       SUM2
------------------------------ ---------- ----------
2008-9-1                               90         90
2008-9-2                               50        140SQL> 

Answer 6

我也来写个：
OPER@tl>select * from test;DAY                      CLASS      PRICE
------------------- ---------- ----------
2008-09-01 00:00:00          1         50
2008-09-01 00:00:00          2         40
2008-09-02 00:00:00          1         30
2008-09-02 00:00:00          2         20
OPER@tl>select day,nvl(lag(sum_fee) over(order by day),0)+sum_fee
  2  from (
  3  select distinct day,sum(price) over(partition by day) sum_fee
  4  from test);DAY                 NVL(LAG(SUM_FEE)OVER(ORDERBYDAY),0)+SUM_FEE
------------------- -------------------------------------------
2008-09-01 00:00:00                                          90
2008-09-02 00:00:00                                         140