数据库

Oracle按年或月进行统计

位置:首页 > 数据库 > Oracle教程,2013-06-10
Oracle按年或月进行统计要求对一张表的数据进行统计表定义(略去用不到的字段)LB01_PURRECEIVEBOOK(PURRECEIVEDATEDATE,--format:2009/11/01RE...

Oracle按年或月进行统计

要求对一张表的数据进行统计 
表定义(略去用不到的字段) 
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


TAGS:Oracle按年统计Oracle按月统计

猜你喜欢

NewHot