Oracle按年如何进行统计？
按照年统计
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...全部
按照年统计
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(+)。收起
