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

)) 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 (一月,二月,三月,四月

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