画面机能要求对一张表的数据进行统计
表定义(略去用不到的字段)
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
|