首页 > 代码库 > 统计过去一年数据,按12月展示,为0 的数据也展示

统计过去一年数据,按12月展示,为0 的数据也展示

第一种方法

按每个月统计出一行数据,然后再行专列。

select decode(counttime,NUMS01,to_char(add_months(sysdate,0),mm),NUMS02,to_char(add_months(sysdate,-1),mm),NUMS03,to_char(add_months(sysdate,-2),mm),NUMS04,to_char(add_months(sysdate,-3),mm),NUMS05,to_char(add_months(sysdate,-4),mm),NUMS06,to_char(add_months(sysdate,-5),mm),NUMS07,to_char(add_months(sysdate,-6),mm),NUMS08,to_char(add_months(sysdate,-7),mm),NUMS09,to_char(add_months(sysdate,-8),mm),NUMS10,to_char(add_months(sysdate,-9),mm),NUMS11,to_char(add_months(sysdate,-10),mm),NUMS12,to_char(add_months(sysdate,-11),mm),NUMS011,to_char(add_months(sysdate,-10),mm),counttime) counttime, trunc(countdata/100,2) countdata from (
select nvl(sum(decode(to_char(commitdatetime,mm),to_char(add_months(sysdate,0),mm),column,0)),0) nums01,nvl(sum(decode(to_char(commitdatetime,mm),to_char(add_months(sysdate,-1),mm),column,0)),0) nums02,
       nvl(sum(decode(to_char(commitdatetime,mm),to_char(add_months(sysdate,-2),mm),column,0)),0) nums03,nvl(sum(decode(to_char(commitdatetime,mm),to_char(add_months(sysdate,-3),mm),column,0)),0) nums04, 
       nvl(sum(decode(to_char(commitdatetime,mm),to_char(add_months(sysdate,-4),mm),column,0)),0) nums05,nvl(sum(decode(to_char(commitdatetime,mm),to_char(add_months(sysdate,-5),mm),column,0)),0) nums06,
       nvl(sum(decode(to_char(commitdatetime,mm),to_char(add_months(sysdate,-6),mm),column,0)),0) nums07,nvl(sum(decode(to_char(commitdatetime,mm),to_char(add_months(sysdate,-7),mm),column,0)),0) nums08,
       nvl(sum(decode(to_char(commitdatetime,mm),to_char(add_months(sysdate,-8),mm),column,0)),0) nums09,nvl(sum(decode(to_char(commitdatetime,mm),to_char(add_months(sysdate,-9),mm),column,0)),0) nums10,
       nvl(sum(decode(to_char(commitdatetime,mm),to_char(add_months(sysdate,-10),mm),column,0)),0) nums11,nvl(sum(decode(to_char(commitdatetime,mm),to_char(add_months(sysdate,-11),mm),column,0)),0) nums12
           from tablename 
           where commitdatetime > to_date(to_char(add_months(sysdate,-12),yyyy-mm)||-1 00:00:01,yyyy-mm-dd hh24:mi:ss)
           )  unpivot (countdata for counttime in (nums01,nums02,nums03,nums04,nums05,nums06,nums07,nums08,nums09,nums10,nums11,nums12) )

第二种方法,先按月统计生成12行2列

select to_char(commitdatetime,yyyy-mm) countdate,trunc(sum(countcolumn)/10000,2) countarea from tablename where commitdatetime > to_date(to_char(add_months(sysdate,-12),yyyy-mm)||-1 00:00:01,yyyy-mm-dd hh24:mi:ss)  group by to_char(commitdatetime,yyyy-mm) order by to_char(commitdatetime,yyyy-mm)

这样统计,为0或者为空的数据将不会出来,接着就是把没有数据的月份也显示出来即可,方法如下

select to_char(add_months(sysdate,1-level),yyyy-mm) from dual connect by level<13

先构造12个月的数据,然后建这个数据左连接刚才统计的表就可以出来想要的数据了。

select t1.rn, t1.cmonth countdate, nvl(t2.countarea,0) countarea from (
select to_char(add_months(sysdate,1-level),yyyy-mm) cmonth,rownum rn from dual connect by level<13 ) t1 left join 
(select to_char(commitdatetime,yyyy-mm) countdate,trunc(sum(buildarea)/10000,2) countarea from trade_stat_brinfo where commitdatetime > to_date(to_char(add_months(sysdate,-12),yyyy-mm)||-1 00:00:01,yyyy-mm-dd hh24:mi:ss)  group by to_char(commitdatetime,yyyy-mm) order by to_char(commitdatetime,yyyy-mm)) t2 on t1.cmonth = t2.countdate
order by t1.rn

技术分享

统计过去一年数据,按12月展示,为0 的数据也展示