请教各位高手，这个统计的存储过程该怎么写呀？

hebo2005
C'est la Vie
等级:
可用分等级：掌柜
总技术分：17116
总技术分排名：792
3

2

发表于：2008-08-18 20:48:561楼得分:90

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)

SQL code

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_C

1 2008-08 144 90 311
2 2008-09 0 0 0

修改删除举报引用回复

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

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(+)