Oracle 按年或月进行统计

12/7/2009来源:Oracle教程人气:5641

csdn上看到的问题,想想写了一个。不对的地方请指正。

画面机能要求对一张表的数据进行统计
表定义(略去用不到的字段)
LB01_PURRECEIVEBOOK

PURRECEIVEDATE    DATE,        --format: 2009/11/01
RECEIVEAMT        NUMBER

① 按年统计时,如果出现空年,则做成该年度金额为0
例:
2009/09/01    1,000
2009/11/01    3,000
2007/12/01    2,000
抽出:
2009    4,000
2008    0
2007    2,000
②按月统计时,出现空月,则做成该月份金额为0
2009/09/01    1,000
2009/11/01    3,000
2009/12/01    2,000
抽出:
2009/09    1,000
2009/10    0
2009/11    3,000
2009/12    2,000


这个可以比较简单的作出来吗?

按照年统计
view plaincopy to clipboardPRint?
WITH tmp_table AS (SELECT MAX(TO_CHAR(T.PURRECEIVEDATE, 'yyyy')) -   
       MIN(TO_CHAR(T.PURRECEIVEDATE, 'yyyy')) AS NUM,   
       MIN(TO_CHAR(T.PURRECEIVEDATE, 'yyyy')) AS MINYEAR   
  FROM LB01_PURRECEIVEBOOK T   
),   
select_table AS (   
SELECT TMP.YEARSUM, TMP.YEAR, TMP.YMD   
  FROM (SELECT SUM(T.RECEIVEAMT) OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'yyyy') ORDER BY T.PURRECEIVEDATE DESC) AS YEARSUM,   
               TO_CHAR(T.PURRECEIVEDATE, 'yyyy') AS YEAR,   
               TO_CHAR(T.PURRECEIVEDATE, 'yyyy/mm/dd') AS YMD,   
               ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'yyyy') ORDER BY T.PURRECEIVEDATE) AS RN   
          FROM LB01_PURRECEIVEBOOK T) TMP   
WHERE TMP.RN = 1  
  
),   
creatyear_table AS (   
SELECT tmp_table.MINYEAR + LEVEL - 1 AS tmp_year FROM DUAL, tmp_table   
CONNECT BY LEVEL <= tmp_table.NUM + 1  
)   
  
SELECT    
ct.tmp_year,   
NVL(st.YEARSUM, 0) AS YEARSUM   
FROM   
creatyear_table ct,   
select_table st   
WHERE   
ct.tmp_year = st.year(+)  
WITH tmp_table AS (SELECT MAX(TO_CHAR(T.PURRECEIVEDATE, 'yyyy')) -
       MIN(TO_CHAR(T.PURRECEIVEDATE, 'yyyy')) AS NUM,
       MIN(TO_CHAR(T.PURRECEIVEDATE, 'yyyy')) AS MINYEAR
  FROM LB01_PURRECEIVEBOOK T
),
select_table AS (
SELECT TMP.YEARSUM, TMP.YEAR, TMP.YMD
  FROM (SELECT SUM(T.RECEIVEAMT) OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'yyyy') ORDER BY T.PURRECEIVEDATE DESC) AS YEARSUM,
               TO_CHAR(T.PURRECEIVEDATE, 'yyyy') AS YEAR,
               TO_CHAR(T.PURRECEIVEDATE, 'yyyy/mm/dd') AS YMD,
               ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'yyyy') ORDER BY T.PURRECEIVEDATE) AS RN
          FROM LB01_PURRECEIVEBOOK T) TMP
WHERE TMP.RN = 1

),
creatyear_table AS (
SELECT tmp_table.MINYEAR + LEVEL - 1 AS tmp_year FROM DUAL, tmp_table
CONNECT BY LEVEL <= tmp_table.NUM + 1
)

SELECT
ct.tmp_year,
NVL(st.YEARSUM, 0) AS YEARSUM
FROM
creatyear_table ct,
select_table st
WHERE
ct.tmp_year = st.year(+)
按照月进行统计
view plaincopy to clipboardprint?
WITH tmp_table AS (SELECT MAX(TO_CHAR(T.PURRECEIVEDATE, 'mm')) -   
       MIN(TO_CHAR(T.PURRECEIVEDATE, 'mm')) AS NUM,   
       MIN(TO_CHAR(T.PURRECEIVEDATE, 'mm')) AS MINMM,   
       MIN(TO_CHAR(T.PURRECEIVEDATE, 'YYYY')) AS MINY   
  FROM LB01_PURRECEIVEBOOK T   
),   
select_table AS (   
SELECT TMP.MMSUM, TMP.MM, TMP.YMD   
  FROM (SELECT SUM(T.RECEIVEAMT) OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'MM') ORDER BY T.PURRECEIVEDATE DESC) AS MMSUM,   
               TO_CHAR(T.PURRECEIVEDATE, 'MM') AS MM,   
               TO_CHAR(T.PURRECEIVEDATE, 'yyyy/mm/dd') AS YMD,   
               ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'MM') ORDER BY T.PURRECEIVEDATE) AS RN   
          FROM LB01_PURRECEIVEBOOK T) TMP   
WHERE TMP.RN = 1  
),   
creatyear_table AS (   
SELECT tmp_table.MINMM + LEVEL - 1 AS TMP_MM,   
       TO_CHAR(TO_DATE(MINY || tmp_table.MINMM + LEVEL - 1, 'YYYY-MM'), 'YYYY-MM') AS TMP_YM   
FROM DUAL, tmp_table   
CONNECT BY LEVEL <= tmp_table.NUM + 1  
)   
SELECT    
ct.TMP_YM,   
TO_CHAR(NVL(ST.MMSUM, 0), '9,999') AS MMSUM   
FROM   
creatyear_table CT,   
select_table ST   
WHERE   
CT.TMP_MM = ST.MM(+)   
ORDER BY    
ct.TMP_YM