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

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

2024-09-17 21:38:35   218人阅读

第一种方法

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

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 的数据也展示


联系
我们