oracle 按年,,,季度,,,月,,,,周,,,统计 |
您所在的位置:网站首页 › oracle按季度分组 › oracle 按年,,,季度,,,月,,,,周,,,统计 |
按照每周进行统计 select to_char(sysdate,'ww') from dual group by to_char(sysdate,'ww');
按照每月进行统计 select to_char(sysdate,'mm') from dual group by to_char(sysdate,'mm');
按照每季度进行统计 select to_char(sysdate,'q') from dual group by to_char(sysdate,'q');
按照每年进行统计 select to_char(sysdate,'yyyy') from dual group by to_char(sysdate,'yyyy');
如下表table1: 日期(exportDate) 数量(amount) -------------- ----------- 14-2月 -08 20 10-3月 -08 2 14-4月 -08 6 14-6月 -08 75 24-10月-09 23 14-11月-09 45 04-8月 -10 5 04-9月 -10 44 04-10月-10 88 注意:为了显示更直观,如下查询已皆按相应分组排序 1.按年份分组 select to_char(exportDate,'yyyy'),sum(amount) from table1 group by to_char(exportDate,'yyyy'); 年份 数量 ----------------------------- 2009 68 2010 137 2008 103 2.按月份分组 select to_char(exportDate,'yyyy-mm'),sum(amount) from table1 group by to_char(exportDate,'yyyy-mm') order by to_char(exportDate,'yyyy-mm'); 月份 数量 ----------------------------- 2008-02 20 2008-03 2 2008-04 6 2008-06 75 2009-10 23 2009-11 45 2010-08 5 2010-09 44 2010-10 88 3.按季度分组 select to_char(exportDate,'yyyy-Q'),sum(amount) from table1 group by to_char(exportDate,'yyyy-Q') order by to_char(exportDate,'yyyy-Q'); 季度 数量 ------------------------------ 2008-1 22 2008-2 81 2009-4 68 2010-3 49 2010-4 88 4.按周分组 select to_char(exportDate,'yyyy-IW'),sum(amount) from table1 group by to_char(exportDate,'yyyy-IW') order by to_char(exportDate,'yyyy-IW'); 周 数量 ------------------------------ 2008-07 20 2008-11 2 2008-16 6 2008-24 75 2009-43 23 2009-46 45 2010-31 5 2010-35 44 2010-40 88 补充: 按季度分组还有个比较笨的方法(参考网络资源) select to_char(exportDate,'yyyy'), sum(decode(to_char(exportDate,'mm'),'01',amount,'02',amount,'03',amount,0)) as 第一季, sum(decode(to_char(exportDate,'mm'),'04',amount,'05',amount,'06',amount,0)) as 第二季, sum(decode(to_char(exportDate,'mm'),'07',amount,'08',amount,'09',amount,0)) as 第三季, sum(decode(to_char(exportDate,'mm'),'10',amount,'11',amount,'12',amount,0)) as 第四季 from table1 group by to_char(exportDate,'yyyy'); 年份 第一季 第二季 第三季 第四季 -------------------------------------------------- 2009 0 0 0 68 2010 0 0 49 88 2008 22 81 0 0 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |