J'ai vu un problème sur csdn, j'y ai réfléchi et j'en ai écrit un. Veuillez me corriger si je me trompe.
La fonction d'écran nécessite la définition d'un tableau statistique pour les données d'un tableau (en omettant les champs inutilisés)
LB01_PURRECEIVEBOOK
(
PURRECEIVEDATE DATE, --format : 2009/11/01
NUMÉRO DE RÉCEPTION
)
① Lors du comptage par année, s'il y a une année vide, le montant pour cette année sera 0
exemple:
01/09/2009 1 000
01/11/2009 3 000
01/12/2007 2 000
Extrait:
2009 4 000
2008 0
2007 2 000
②Lors du calcul par mois, s'il y a un mois vide, le montant de ce mois sera fixé à 0
01/09/2009 1 000
01/11/2009 3 000
2009/12/01 2 000
Extrait:
2009/09 1 000
2009/10 0
2009/11 3 000
2009/12 2 000
Cela peut-il être fait de manière relativement simple ?
Selon les statistiques annuelles
afficher la copie ordinaire dans le presse-papiers ?
AVEC tmp_table AS (SELECT MAX(TO_CHAR(T.PURRECEIVEDATE, 'aaaa')) -
MIN(TO_CHAR(T.PURRECEIVEDATE, 'aaaa')) AS NUM,
MIN(TO_CHAR(T.PURRECEIVEDATE, 'aaaa')) AS MINIYEAR
DE 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, 'aaaa') AS ANNÉE,
TO_CHAR(T.PURRECEIVEDATE, 'aaaa/mm/jj') AS YMD,
ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'aaaa') ORDER BY T.PURRECEIVEDATE) AS RN
DE LB01_PURRECEIVEBOOK T) TMP
OÙ TMP.RN = 1
),
createyear_table AS (
SELECT tmp_table.MINYEAR + LEVEL - 1 AS tmp_year FROM DUAL, tmp_table
CONNEXION PAR NIVEAU <= tmp_table.NUM + 1
)
SÉLECTIONNER
ct.tmp_année,
NVL(st.YEARSUM, 0) AS YEARSUM
DEPUIS
createyear_table ct,
select_table st
OÙ
ct.tmp_year = st.année(+)
AVEC tmp_table AS (SELECT MAX(TO_CHAR(T.PURRECEIVEDATE, 'aaaa')) -
MIN(TO_CHAR(T.PURRECEIVEDATE, 'aaaa')) AS NUM,
MIN(TO_CHAR(T.PURRECEIVEDATE, 'aaaa')) AS MINIYEAR
DE 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, 'aaaa') AS ANNÉE,
TO_CHAR(T.PURRECEIVEDATE, 'aaaa/mm/jj') AS YMD,
ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'aaaa') ORDER BY T.PURRECEIVEDATE) AS RN
DE LB01_PURRECEIVEBOOK T) TMP
OÙ TMP.RN = 1
),
createyear_table AS (
SELECT tmp_table.MINYEAR + LEVEL - 1 AS tmp_year FROM DUAL, tmp_table
CONNEXION PAR NIVEAU <= tmp_table.NUM + 1
)
SÉLECTIONNER
ct.tmp_année,
NVL(st.YEARSUM, 0) AS YEARSUM
DEPUIS
createyear_table ct,
select_table st
OÙ
ct.tmp_year = st.année(+)
Statistiques par mois
afficher la copie ordinaire dans le presse-papiers ?
AVEC 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
DE LB01_PURRECEIVEBOOK T
),
select_table AS (
SELECTIONNER 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, 'aaaa/mm/jj') AS YMD,
ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T.PURRECEIVEDATE, 'MM') ORDER BY T.PURRECEIVEDATE) AS RN
DE LB01_PURRECEIVEBOOK T) TMP
OÙ TMP.RN = 1
),
createyear_table AS (
SELECT tmp_table.MINMM + NIVEAU - 1 AS TMP_MM,
TO_CHAR(TO_DATE(MINY || tmp_table.MINMM + NIVEAU - 1, 'AAAA-MM'), 'AAAA-MM') AS TMP_YM
DEPUIS DOUBLE, tmp_table
CONNEXION PAR NIVEAU <= tmp_table.NUM + 1
)
SÉLECTIONNER
ct.TMP_YM,
TO_CHAR(NVL(ST.MMSUM, 0), '9 999') AS MMSUM
DEPUIS
createyear_table CT,
select_table ST
OÙ
CT.TMP_MM = ST.MM(+)
COMMANDER PAR
ct.TMP_YM
-