Oracle按年或月进行统计
发布时间：2011/2/18 11:49:55 来源：城市学习网 编辑：ziteng
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
-
