怎么获取当月每日收入报表

Question

有表如下：table1编号     支出    收入    时间
ID payout   income createtime
1   300     400    2012-3-1
2         100   150    2012-3-2
3   146     352    2012-3-4
4         100   150    2012-3-5
5   300     400    2012-3-6
6         100   150    2012-3-7
怎么获得以下结果，用SELECT编号 支出 收入   时间 盈利
1 300 400 2012-3-1 100
2 100 150 2012-3-2 150  --这里是当日的盈利再加上以前的盈利
3 0 0 2012-3-3 150 --因为这里星期六，所以放假，盈利就为前几日的。。
4
5
6 2012-3-31 XXX --一直获取到31号，也就是这个月的收入。

Answer 1

SELECT ID,PAYOUT,INCOME,CREATETIME,SUM((INCOME - PAYOUT))OVER(ORDER BY ID) AS GAIN
FROM TABLE1
GROUP BY ID,PAYOUT,INCOME,CREATETIME;

Answer 2

SQL> select * from a5;
 
        ID     PAYOUT     INCOME CREATETIME
---------- ---------- ---------- --------------
         1        300        400 2012-3-01
         2        100        150 2012-3-02
         3        146        352 2012-3-04
         4        100        150 2012-3-05
         5        300        400 2012-3-06
         6        100        150 2012-3-07
 
6 rows selected
 
SQL> 
SQL> select a5.payout, a5.income, a5.income - a5.payout, t.tcol
  2    from a5,
  3         (select '2012-3-' || lpad(rownum, 2, '0') tcol
  4            from dual
  5          connect by rownum <= 31) t
  6   where t.tcol = a5.createtime(+)
  7  ;
 
    PAYOUT     INCOME A5.INCOME-A5.PAYOUT TCOL
---------- ---------- ------------------- ---------------
       300        400                 100 2012-3-01
       100        150                  50 2012-3-02
       146        352                 206 2012-3-04
       100        150                  50 2012-3-05
       300        400                 100 2012-3-06
       100        150                  50 2012-3-07
                                          2012-3-13
                                          2012-3-31
                                          2012-3-19
                                          2012-3-16
                                          2012-3-23
                                          2012-3-10
                                          2012-3-03
                                          2012-3-17
                                          2012-3-24
                                          2012-3-08
                                          2012-3-22
                                          2012-3-28
                                          2012-3-25
                                          2012-3-29
 
    PAYOUT     INCOME A5.INCOME-A5.PAYOUT TCOL
---------- ---------- ------------------- ---------------
                                          2012-3-26
                                          2012-3-14
                                          2012-3-12
                                          2012-3-09
                                          2012-3-30
                                          2012-3-27
                                          2012-3-20
                                          2012-3-21
                                          2012-3-18
                                          2012-3-15
                                          2012-3-11
 
31 rows selected