oracle按半小时统计,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 (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(+)。

全部