Oracle 按年或月进行统计

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 这个可以比较简单的作出来吗？   按照年统计 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 SUM(T.RECEIVEAMT) AS YEARSUM, TO_CHAR(T.PURRECEIVEDATE, 'YYYY') AS YEAR FROM LB01_PURRECEIVEBOOK T GROUP BY TO_CHAR(T.PURRECEIVEDATE, 'YYYY') ), 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(+) ORDER BY ct.tmp_year DESC 按照月进行统计 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 SUM(T.RECEIVEAMT) AS MMSUM, TO_CHAR(T.PURRECEIVEDATE, 'MM') AS MM FROM LB01_PURRECEIVEBOOK T GROUP BY TO_CHAR(T.PURRECEIVEDATE, 'MM') ), 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