我有一个表test,有如下字段(和记录):
  addTime(日期)       A(字段A) B(字段B) C(字段C)
   2008-8-1              20         30         40
   2008-8-2              10         30         40 
   2008-8-3              13         39         56
   2008-8-4              25        56         43
   2008-8-5              36         30         25
   2008-8-6              20         36         67
   2008-8-7              20         90         40字段addtime为日期型
其余为number型我需要用存储过程求出一个月汇总和一个年汇总月汇总的在页面上显示是:
   月份       month_A   month_B   month_C 
  2008-8     200        90       200
  2008-9     0          0         0其中:month_A 是指定月份A 字段值的求和
      month_B 是指定月(有记录的)B字段最后一天的值,例如现在数据的8月份最大的日期是8月7号,所2008-8-7是8月最后一天记录。
      month_C和month_A 是类似的。   这个月份会是一个参数,到时候是动态的,可以动态显示不同的月份,或者是多个月份的。
年汇总比月汇总还麻烦一点:
    年份         year_A   year_B   year_C 
  2008年         200        90       200
  2009年          100       40        37year_A 指定年份说有A字段的求和
year_B 指定年份B字段最后一天的值
year_C 指定年份中C字段到目前(有记录)月份最后一天值得求和。这个最麻烦,要分别把数据库里本年度的每个月最后一天的值求和。这个年份也是一个参数,为空的话,就是显示库中有记录的所有年份。想用各用存储过程,分别把年汇总和月汇总能拼成这样一个数据集,请大家帮忙。

解决方案 »

  1.   

    SQL可以写出来,要写存储过程返回数据集,麻烦点月的,我先写一段,你可以参考下WITH TEST AS
         (SELECT TO_DATE ('2008-8-1', 'yyyy-mm-dd') addtime, 20 a, 30 b, 40 c
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('2008-8-2', 'yyyy-mm-dd') addtime, 10, 30, 40
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('2008-8-3', 'yyyy-mm-dd') addtime, 13, 39, 56
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('2008-8-4', 'yyyy-mm-dd') addtime, 25, 56, 43
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('2008-8-5', 'yyyy-mm-dd') addtime, 36, 30, 25
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('2008-8-6', 'yyyy-mm-dd') addtime, 20, 36, 67
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('2008-8-7', 'yyyy-mm-dd') addtime, 20, 90, 40
            FROM DUAL)
    SELECT m.months, NVL (t.months_a, 0) months_a, NVL (months_b, 0) months_b,
           NVL (months_c, 0) months_c
      FROM (SELECT TO_CHAR (ADD_MONTHS (TO_DATE (:start_date, 'yyyy-mm'),
                                        ROWNUM - 1
                                       ),
                            'yyyy-mm'
                           ) months
              FROM all_objects
             WHERE ROWNUM <=
                        MONTHS_BETWEEN (TO_DATE (:end_date, 'yyyy-mm'),
                                        TO_DATE (:start_date, 'yyyy-mm')
                                       )
                      + 1) m,
           (SELECT   TO_CHAR (addtime, 'yyyy-mm') months, SUM (a) months_a,
                     months_b, SUM (c) months_c
                FROM (SELECT a.*,
                             LAST_VALUE (b) OVER (PARTITION BY TO_CHAR
                                                                 (addtime,
                                                                  'yyyy-mm'
                                                                 ) ORDER BY addtime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                                                                         months_b
                        FROM TEST a
                       WHERE addtime BETWEEN TO_DATE (:start_date, 'yyyy-mm')
                                         AND   TO_DATE (:end_date, 'yyyy-mm')
                                             + 0.99999)
            GROUP BY TO_CHAR (addtime, 'yyyy-mm'), months_b) t
     WHERE m.months = t.months(+)结果
    Row# MONTHS MONTHS_A MONTHS_B MONTHS_C1 2008-08 144 90 311
    2 2008-09 0 0 0
      

  2.   

    多谢楼上的,不过我没看明白思路。
    SELECT m.months, NVL (t.months_a, 0) months_a, NVL (months_b, 0) months_b,
           NVL (months_c, 0) months_c
      FROM (SELECT TO_CHAR (ADD_MONTHS (TO_DATE (:start_date, 'yyyy-mm'),
                       ADD_MONTHS --这个好像不是个函数吧             
      

  3.   

    FROM all_objects,这个all_objects是什么意思呀?
      

  4.   

    all_objects我查的下有的说是视图,有点说是系统表,到底是啥呀
      

  5.   

    是系统表
    如果不能访问此表
    可以用以下语句代替
    SELECT TO_CHAR (ADD_MONTHS (TO_DATE (:start_date, 'yyyy-mm'),
                                        ROWNUM - 1
                                       ),
                            'yyyy-mm'
                           ) months
              FROM dual
             connect by ROWNUM <=
                        MONTHS_BETWEEN (TO_DATE (:end_date, 'yyyy-mm'),
                                        TO_DATE (:start_date, 'yyyy-mm')
                                       )
                      + 1
      

  6.   

           (SELECT   TO_CHAR (addtime, 'yyyy-mm') months, SUM (a) months_a,
                     months_b, SUM (c) months_c
                FROM (SELECT a.*,
                             LAST_VALUE (b) OVER (PARTITION BY TO_CHAR
                                                                 (addtime,
                                                                  'yyyy-mm'
                                                                 ) ORDER BY addtime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                                                                         months_b
                        FROM TEST a
                       WHERE addtime BETWEEN TO_DATE (:start_date, 'yyyy-mm')
                                         AND   TO_DATE (:end_date, 'yyyy-mm')
                                             + 0.99999)
    这段没太明白,尤其最后这个+0、99999,很少见过。
      

  7.   

    +0.99999就是当天的23:59:59
    当然也可以用<TO_DATE (:end_date, 'yyyy-mm') +1
      

  8.   

      addTime(日期)      A(字段A) B(字段B) C(字段C)  typename
      2008-8-1              20        30        40      x
      2008-8-2              10        30        42      x
      2008-8-1              11        32        44      y
      2008-8-2              12        33        45      y
      2008-8-3              13        39        56      x
      2008-8-4              25        56        43      y
      2008-8-5              36        30        25      y
      2008-8-6              20        36        67      y
      2008-8-7              20        90        40      x
      2008-8-7              30        24        23      z
    现在我的这汇总比原来又复杂了一点,多了个字段typename,里面有不同的值,汇总要按照这个typename进行区分。
    如:月汇总会是:
     月份      month_A  month_B  month_C  typename
      2008-8    200        90      200     x
      2008-8    223        0        0      y
      2008-9    200        90      200     x
      2008-9    34         0        0      y这样
      

  9.   

    存储过程的总体性能会比SQL高思想是不正确的,反而在大多数情况下SQL性能会好些因为是集合化的处理.
      

  10.   

    addTime(日期)      A(字段A) B(字段B) C(字段C)  typename 
      2008-8-1              20        30        40      x 
      2008-8-2              10        30        42      x 
      2008-8-1              11        32        44      y 
      2008-8-2              12        33        45      y 
      2008-8-3              13        39        56      x 
      2008-8-4              25        56        43      y 
      2008-8-5              36        30        25      y 
      2008-8-6              20        36        67      y 
      2008-8-7              20        90        40      x 
      2008-8-7              30        24        23      z 
    现在我的这汇总比原来又复杂了一点,多了个字段typename,里面有不同的值,汇总要按照这个typename进行区分。 
    如:月汇总会是: 
    月份      month_A  month_B  month_C  typename 
      2008-8    200        90      200    x 
      2008-8    223        0        0      y 
      2008-9    200        90      200    x 
      2008-9    34        0        0      y 请大家帮忙回答下这个问题,能不能在hebo2005 sql上进行改造
      

  11.   

    SELECT m.months, NVL (t.months_a, 0) months_a, NVL (months_b, 0) months_b,
           NVL (months_c, 0) months_c,typename 
      FROM (SELECT TO_CHAR (ADD_MONTHS (TO_DATE (:start_date, 'yyyy-mm'),
                                        ROWNUM - 1
                                       ),
                            'yyyy-mm'
                           ) months
              FROM all_objects
             WHERE ROWNUM <=
                        MONTHS_BETWEEN (TO_DATE (:end_date, 'yyyy-mm'),
                                        TO_DATE (:start_date, 'yyyy-mm')
                                       )
                      + 1) m,
           (SELECT   TO_CHAR (addtime, 'yyyy-mm') months, SUM (a) months_a,
                     months_b, SUM (c) months_c,typename 
                FROM (SELECT a.*,
                             LAST_VALUE (b) OVER (PARTITION BY TO_CHAR
                                                                 (addtime,
                                                                  'yyyy-mm'
                                                                 ),typename  ORDER BY addtime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                                                                         months_b
                        FROM TEST a
                       WHERE addtime BETWEEN TO_DATE (:start_date, 'yyyy-mm')
                                         AND   TO_DATE (:end_date, 'yyyy-mm')
                                             + 0.99999)
            GROUP BY TO_CHAR (addtime, 'yyyy-mm'), months_b,typename ) t
     WHERE m.months = t.months(+)