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
-