SQL> select * from bill;
 
BILLID                                   BILLDATE        SEATID       SUM  VIP PAYED MADE BILLUUID BILLCHECK
---------------------------------------- -------------- ------- --------- ---- ----- ---- -------- ---------
20111108137                              20111108103947       9            1.0     2    1      137         2
20111108139                              20111108104009       4            1.0     2    2      139         2
20111108141                              20111108104024       7            1.0     2    2      141         2
20111108143                              20111108104326       7            1.0     2    2      143         2
20111109172                              20111109090803       5            1.0     1    2      172         1
20111110192                              20111110124946      10            1.0     2    2      192         2
20111101193                              20111101125024       1            1.0     2    2      193         2
20111030194                              20111030125108       2            1.0     2    2      194         2
20110915195                              20110915125128       3            1.0     2    2      195         2
20110901196                              20110901125142       4            1.0     2    2      196         2
20110922197                              20110922125156       5            1.0     2    2      197         2
20110928198                              20110928125210       1            1.0     2    2      198         2
20111006199                              20111006125230       2            1.0     2    2      199         2
20111013200                              20111013125243       3            1.0     2    2      200         2
20111027201                              20111027125256       4            1.0     2    2      201         2
20111103202                              20111103125316       5            1.0     2    2      202         2
20111108132                              20111108101744       1       6.0  1.0     1    2      132         1
20111108133                              20111108101746       2      21.0  1.0     2    2      133         1
20111109134                              20111109101750       3      12.0  1.0     2    2      134         1
20111108135                              20111108101754       4            1.0     2    1      135         2
 
BILLID                                   BILLDATE        SEATID       SUM  VIP PAYED MADE BILLUUID BILLCHECK
---------------------------------------- -------------- ------- --------- ---- ----- ---- -------- ---------
20111109173                              20111109141248       5            1.0     2    2      173         2
20111130203                              20111130125341       6            1.0     2    2      203         2
20111207204                              20111207125352       7            1.0     2    2      204         2
 怎么将上面的数据按查询单位:周、月、年查询统计出来(只用查在单位时间内的sum之和,也就是sum(sum));

解决方案 »

  1.   

    上面字段BILLUUID(即时间字段)默认为:
    to_char(sysdate,'yyyymmddHH24MISS')
      

  2.   

    先生成 每筆記錄的 年,月,日字段 。在rollup
      

  3.   

    rollup   配合   goup   by   命令使用,可以提供信息汇总功能(类似于"小计")   
      下面是一个简单例子:   
        
      SQL>   select   job,deptno,sal   from   emp;   
        
      JOB                     DEPTNO               SAL   
      ---------   ---------   ---------   
      CLERK                         20               800   
      SALESMAN                   30             1600   
      SALESMAN                   30             1250   
      MANAGER                     20             2975   
      SALESMAN                   30             1250   
      MANAGER                     30             2850   
      MANAGER                     10             2450   
      ANALYST                     20             3000   
      PRESIDENT                 10             5000   
      SALESMAN                   30             1500   
      CLERK                         20             1100   
      CLERK                         30               950   
      ANALYST                     20             3000   
      CLERK                         10             1300   
        
      已选择14行。   
        
      SQL>   select   job,deptno,sum(sal)   total_sal   from   emp   group   by   rollup(job,deptno);   
        
      JOB                     DEPTNO   TOTAL_SAL   
      ---------   ---------   ---------   
      ANALYST                     20             6000   
      ANALYST                                     6000   
      CLERK                         10             1300   
      CLERK                         20             1900   
      CLERK                         30               950   
      CLERK                                         4150   
      MANAGER                     10             2450   
      MANAGER                     20             2975   
      MANAGER                     30             2850   
      MANAGER                                     8275   
      PRESIDENT                 10             5000   
      PRESIDENT                                 5000   
      SALESMAN                   30             5600   
      SALESMAN                                   5600   
                                                      29025   
      

  4.   

    1.按照每周进行统计 
     SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'ww')
    2.按照每月进行统计
     SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'mm')
    3.按照每季度进行统计
     SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'q')
    4.按照每年进行统计
     SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'YYYY')