SQL查询一年的十二个月份，形成报表

90 阅读 0 评论 60 点赞

我是靠谱客的博主洁净路人，最近开发中收集的这篇文章主要介绍SQL查询一年的十二个月份，形成报表，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

SELECT YEAR(日期字段) 年度,SUM(CASE WHEN MONTH(日期字段) =1 THEN 统计的字段 ELSE 0 END) 一月,   
SUM(CASE WHEN MONTH(日期字段) =2 THEN 统计的字段 ELSE 0 END) 二月,
SUM(CASE WHEN MONTH(日期字段) =3 THEN 统计的字段 ELSE 0 END) 三月,
SUM(CASE WHEN MONTH(日期字段) =4 THEN 统计的字段 ELSE 0 END) 四月,
SUM(CASE WHEN MONTH(日期字段) =5 THEN 统计的字段 ELSE 0 END) 五月,
SUM(CASE WHEN MONTH(日期字段) =6 THEN 统计的字段 ELSE 0 END) 六月,
SUM(CASE WHEN MONTH(日期字段) =7 THEN 统计的字段 ELSE 0 END) 七月,
SUM(CASE WHEN MONTH(日期字段) =8 THEN 统计的字段 ELSE 0 END) 八月,
SUM(CASE WHEN MONTH(日期字段) =9 THEN 统计的字段 ELSE 0 END) 九月,
SUM(CASE WHEN MONTH(日期字段) =10 THEN 统计的字段 ELSE 0 END) 十月,
SUM(CASE WHEN MONTH(日期字段) =11 THEN 统计的字段 ELSE 0 END) 十一月,
SUM(CASE WHEN MONTH(日期字段) =12 THEN 统计的字段 ELSE 0 END) 十二月,
    FROM 表

        GROUP BY YEAR(日期字段)

Oracle统计某一年中的1-12个月的数据总和

实战案例

查询2015年1月到12个月的所有实有人口数量和往年2014年1月到12个月的实有人口数量，没有的月份显示人口数量为0.类似效果如图
这里写图片描述

创建表

create table PERSONSITUATION
(
  id    NUMBER not null,
  rdate DATE,
  nums  NUMBER
)

插入数据

insert into PERSONSITUATION (id, rdate, nums) values (1, to_date('26-01-2015', 'dd-mm-yyyy'), 131);
insert into PERSONSITUATION (id, rdate, nums) values (2, to_date('27-01-2013', 'dd-mm-yyyy'), 232);
insert into PERSONSITUATION (id, rdate, nums) values (3, to_date('18-10-2013', 'dd-mm-yyyy'), 222);
insert into PERSONSITUATION (id, rdate, nums) values (4, to_date('20-01-2015', 'dd-mm-yyyy'), 232);
insert into PERSONSITUATION (id, rdate, nums) values (5, to_date('28-01-2015', 'dd-mm-yyyy'), 422);
insert into PERSONSITUATION (id, rdate, nums) values (6, to_date('26-02-2015', 'dd-mm-yyyy'), 232);
insert into PERSONSITUATION (id, rdate, nums) values (7, to_date('29-01-2014', 'dd-mm-yyyy'), 225);
insert into PERSONSITUATION (id, rdate, nums) values (8, to_date('31-01-2015', 'dd-mm-yyyy'), 111);
insert into PERSONSITUATION (id, rdate, nums) values (9, to_date('25-01-2013', 'dd-mm-yyyy'), 211);
insert into PERSONSITUATION (id, rdate, nums) values (10, to_date('25-01-2013', 'dd-mm-yyyy'), 251);
insert into PERSONSITUATION (id, rdate, nums) values (11, to_date('25-01-2013', 'dd-mm-yyyy'), 262);
insert into PERSONSITUATION (id, rdate, nums) values (12, to_date('25-08-2015', 'dd-mm-yyyy'), 233);
insert into PERSONSITUATION (id, rdate, nums) values (13, to_date('25-01-2013', 'dd-mm-yyyy'), 211);
insert into PERSONSITUATION (id, rdate, nums) values (14, to_date('25-02-2014', 'dd-mm-yyyy'), 222);
insert into PERSONSITUATION (id, rdate, nums) values (15, to_date('25-03-2012', 'dd-mm-yyyy'), 209);
insert into PERSONSITUATION (id, rdate, nums) values (16, to_date('25-01-2012', 'dd-mm-yyyy'), 219);

常见的统计中如下Sql语句，只查询到了数据中有的月，如果没有就什么也没有显示，和明显不符合1-12个月的12条数据统计

select to_char(rdate,'yyyy-mm') rdate,sum(nums) nums from personsituation where to_char(rdate,'yyyy')='2015' group by to_char(rdate,'yyyy-mm') order by rdate

这里写图片描述
正确的分析是：必须是12条数据，而且是统计的结果的12条数据，从这里入手编写如下Sql语句，显示12个列的统计数据，先查询出一年的数据，然后再连接另外一条数据拼接

select sum(decode(to_char(rdate,'mm'),'01',nums,0)) nums01,sum(decode(to_char(rdate,'mm'),'02',nums,0)) nums02,
       sum(decode(to_char(rdate,'mm'),'03',nums,0)) nums03,sum(decode(to_char(rdate,'mm'),'04',nums,0)) nums04, 
       sum(decode(to_char(rdate,'mm'),'05',nums,0)) nums05,sum(decode(to_char(rdate,'mm'),'06',nums,0)) nums06,
       sum(decode(to_char(rdate,'mm'),'07',nums,0)) nums07,sum(decode(to_char(rdate,'mm'),'08',nums,0)) nums08,
       sum(decode(to_char(rdate,'mm'),'09',nums,0)) nums09,sum(decode(to_char(rdate,'mm'),'10',nums,0)) nums10,
       sum(decode(to_char(rdate,'mm'),'11',nums,0)) nums11,sum(decode(to_char(rdate,'mm'),'12',nums,0)) nums12
           from personsituation 
           where to_char(rdate,'yyyy')='2015'

这里写图片描述
而前端页面显示的结果有两种可能性，一种是横向展示，一种是纵向展示，可以使用下面的sql语句进行列转行的转换得到如下结果

select * from (
  select sum(decode(to_char(rdate,'mm'),'01',nums,0)) nums01,sum(decode(to_char(rdate,'mm'),'02',nums,0)) nums02,
     sum(decode(to_char(rdate,'mm'),'03',nums,0)) nums03,sum(decode(to_char(rdate,'mm'),'04',nums,0)) nums04, 
     sum(decode(to_char(rdate,'mm'),'05',nums,0)) nums05,sum(decode(to_char(rdate,'mm'),'06',nums,0)) nums06,
     sum(decode(to_char(rdate,'mm'),'07',nums,0)) nums07,sum(decode(to_char(rdate,'mm'),'08',nums,0)) nums08,
     sum(decode(to_char(rdate,'mm'),'09',nums,0)) nums09,sum(decode(to_char(rdate,'mm'),'10',nums,0)) nums10,
     sum(decode(to_char(rdate,'mm'),'11',nums,0)) nums11,sum(decode(to_char(rdate,'mm'),'12',nums,0)) nums12
         from personsituation 
         where to_char(rdate,'yyyy')='2015'
)  unpivot (sum2015 for years in (nums01,nums02,nums03,nums04,nums05,nums06,nums07,nums08,nums09,nums10,nums11,nums12) )

这里写图片描述

最后一步就是和往年的数据对比使用left join查询

select A.years,A.SUM2015,B.SUM2014 from    
  (select * from (
    select sum(decode(to_char(rdate,'mm'),'01',nums,0)) nums01,sum(decode(to_char(rdate,'mm'),'02',nums,0)) nums02,
       sum(decode(to_char(rdate,'mm'),'03',nums,0)) nums03,sum(decode(to_char(rdate,'mm'),'04',nums,0)) nums04, 
       sum(decode(to_char(rdate,'mm'),'05',nums,0)) nums05,sum(decode(to_char(rdate,'mm'),'06',nums,0)) nums06,
       sum(decode(to_char(rdate,'mm'),'07',nums,0)) nums07,sum(decode(to_char(rdate,'mm'),'08',nums,0)) nums08,
       sum(decode(to_char(rdate,'mm'),'09',nums,0)) nums09,sum(decode(to_char(rdate,'mm'),'10',nums,0)) nums10,
       sum(decode(to_char(rdate,'mm'),'11',nums,0)) nums11,sum(decode(to_char(rdate,'mm'),'12',nums,0)) nums12
           from personsituation 
           where to_char(rdate,'yyyy')='2015'
  ) unpivot (sum2015 for years in (nums01,nums02,nums03,nums04,nums05,nums06,nums07,nums08,nums09,nums10,nums11,nums12) )) A
left join
  (select * from (
    select sum(decode(to_char(rdate,'mm'),'01',nums,0)) nums01,sum(decode(to_char(rdate,'mm'),'02',nums,0)) nums02,
       sum(decode(to_char(rdate,'mm'),'03',nums,0)) nums03,sum(decode(to_char(rdate,'mm'),'04',nums,0)) nums04, 
       sum(decode(to_char(rdate,'mm'),'05',nums,0)) nums05,sum(decode(to_char(rdate,'mm'),'06',nums,0)) nums06,
       sum(decode(to_char(rdate,'mm'),'07',nums,0)) nums07,sum(decode(to_char(rdate,'mm'),'08',nums,0)) nums08,
       sum(decode(to_char(rdate,'mm'),'09',nums,0)) nums09,sum(decode(to_char(rdate,'mm'),'10',nums,0)) nums10,
       sum(decode(to_char(rdate,'mm'),'11',nums,0)) nums11,sum(decode(to_char(rdate,'mm'),'12',nums,0)) nums12
           from personsituation 
           where to_char(rdate,'yyyy')='2014'
  ) unpivot (sum2014 for years in (nums01,nums02,nums03,nums04,nums05,nums06,nums07,nums08,nums09,nums10,nums11,nums12) )) B 
on A.years = B.years

这里写图片描述

实现与效果图一样的数据，可以将sql中的nums01...nums12改为1月...12月

select A.years,A.SUM1,B.SUM2 from    
  (select * from (
    select sum(decode(to_char(rdate,'mm'),'01',nums,0)) 一月,sum(decode(to_char(rdate,'mm'),'02',nums,0)) 二月,
       sum(decode(to_char(rdate,'mm'),'03',nums,0)) 三月,sum(decode(to_char(rdate,'mm'),'04',nums,0)) 四月, 
       sum(decode(to_char(rdate,'mm'),'05',nums,0)) 五月,sum(decode(to_char(rdate,'mm'),'06',nums,0)) 六月,
       sum(decode(to_char(rdate,'mm'),'07',nums,0)) 七月,sum(decode(to_char(rdate,'mm'),'08',nums,0)) 八月,
       sum(decode(to_char(rdate,'mm'),'09',nums,0)) 九月,sum(decode(to_char(rdate,'mm'),'10',nums,0)) 十月,
       sum(decode(to_char(rdate,'mm'),'11',nums,0)) 十一月,sum(decode(to_char(rdate,'mm'),'12',nums,0)) 十二月
           from personsituation 
           where to_char(rdate,'yyyy')= '2015'
  ) unpivot (sum1 for years in (一月,二月,三月,四月,五月,六月,七月,八月, 九月,十月,十一月,十二月) )) A
left join
  (select * from (
    select sum(decode(to_char(rdate,'mm'),'01',nums,0)) 一月,sum(decode(to_char(rdate,'mm'),'02',nums,0)) 二月,
       sum(decode(to_char(rdate,'mm'),'03',nums,0)) 三月,sum(decode(to_char(rdate,'mm'),'04',nums,0)) 四月, 
       sum(decode(to_char(rdate,'mm'),'05',nums,0)) 五月,sum(decode(to_char(rdate,'mm'),'06',nums,0)) 六月,
       sum(decode(to_char(rdate,'mm'),'07',nums,0)) 七月,sum(decode(to_char(rdate,'mm'),'08',nums,0)) 八月,
       sum(decode(to_char(rdate,'mm'),'09',nums,0)) 九月,sum(decode(to_char(rdate,'mm'),'10',nums,0)) 十月,
       sum(decode(to_char(rdate,'mm'),'11',nums,0)) 十一月,sum(decode(to_char(rdate,'mm'),'12',nums,0)) 十二月
           from personsituation 
           where to_char(rdate,'yyyy')= '2014'
  ) unpivot (sum2 for years in (一月,二月,三月,四月,五月,六月,七月,八月, 九月,十月,十一月,十二月) )) B 
on A.years = B.years