Oracle统计某一年中的1-12个月的数据总和（非常实用）

实战案例

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

创建表

 

?

1

2

3

4

5

6

createtable PERSONSITUATION

(

  id    NUMBER notnull,

  rdateDATE,

  nums  NUMBER

)

 

插入数据

 

 

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

insertinto PERSONSITUATION (id, rdate, nums) values(1, to_date('26-01-2015','dd-mm-yyyy'), 131);

insertinto PERSONSITUATION (id, rdate, nums) values(2, to_date('27-01-2013','dd-mm-yyyy'), 232);

insertinto PERSONSITUATION (id, rdate, nums) values(3, to_date('18-10-2013','dd-mm-yyyy'), 222);

insertinto PERSONSITUATION (id, rdate, nums) values(4, to_date('20-01-2015','dd-mm-yyyy'), 232);

insertinto PERSONSITUATION (id, rdate, nums) values(5, to_date('28-01-2015','dd-mm-yyyy'), 422);

insertinto PERSONSITUATION (id, rdate, nums) values(6, to_date('26-02-2015','dd-mm-yyyy'), 232);

insertinto PERSONSITUATION (id, rdate, nums) values(7, to_date('29-01-2014','dd-mm-yyyy'), 225);

insertinto PERSONSITUATION (id, rdate, nums) values(8, to_date('31-01-2015','dd-mm-yyyy'), 111);

insertinto PERSONSITUATION (id, rdate, nums) values(9, to_date('25-01-2013','dd-mm-yyyy'), 211);

insertinto PERSONSITUATION (id, rdate, nums) values(10, to_date('25-01-2013','dd-mm-yyyy'), 251);

insertinto PERSONSITUATION (id, rdate, nums) values(11, to_date('25-01-2013','dd-mm-yyyy'), 262);

insertinto PERSONSITUATION (id, rdate, nums) values(12, to_date('25-08-2015','dd-mm-yyyy'), 233);

insertinto PERSONSITUATION (id, rdate, nums) values(13, to_date('25-01-2013','dd-mm-yyyy'), 211);

insertinto PERSONSITUATION (id, rdate, nums) values(14, to_date('25-02-2014','dd-mm-yyyy'), 222);

insertinto PERSONSITUATION (id, rdate, nums) values(15, to_date('25-03-2012','dd-mm-yyyy'), 209);

insertinto PERSONSITUATION (id, rdate, nums) values(16, to_date('25-01-2012','dd-mm-yyyy'), 219);

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

?

1

selectto_char(rdate,'yyyy-mm') rdate,sum(nums) nums frompersonsituation whereto_char(rdate,'yyyy')='2015'group by to_char(rdate,'yyyy-mm')orderby rdate

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

 

 

?

1

2

3

4

5

6

7

8

selectsum(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

           frompersonsituation

           whereto_char(rdate,'yyyy')='2015'

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

 

 

?

1

2

3

4

5

6

7

8

9

10

select* from(

  selectsum(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

         frompersonsituation

         whereto_char(rdate,'yyyy')='2015'

)  unpivot (sum2015 foryears in(nums01,nums02,nums03,nums04,nums05,nums06,nums07,nums08,nums09,nums10,nums11,nums12) )

 

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

 

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

selectA.years,A.SUM2015,B.SUM2014 from    

  (select* from(

    selectsum(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

           frompersonsituation

           whereto_char(rdate,'yyyy')='2015'

  ) unpivot (sum2015 foryears in(nums01,nums02,nums03,nums04,nums05,nums06,nums07,nums08,nums09,nums10,nums11,nums12) )) A

leftjoin

  (select* from(

    selectsum(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

           frompersonsituation

           whereto_char(rdate,'yyyy')='2014'

  ) unpivot (sum2014 foryears in(nums01,nums02,nums03,nums04,nums05,nums06,nums07,nums08,nums09,nums10,nums11,nums12) )) B

onA.years = B.years

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

 

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

selectA.years,A.SUM1,B.SUM2 from    

  (select* from(

    selectsum(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)) 十二月

           frompersonsituation

           whereto_char(rdate,'yyyy')='2015'

  ) unpivot (sum1 foryears in(一月,二月,三月,四月,五月,六月,七月,八月, 九月,十月,十一月,十二月) )) A

leftjoin

  (select* from(

    selectsum(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)) 十二月

           frompersonsituation

           whereto_char(rdate,'yyyy')='2014'

  ) unpivot (sum2 foryears in(一月,二月,三月,四月,五月,六月,七月,八月, 九月,十月,十一月,十二月) )) B

onA.years = B.years