《oracle按时间分组统计的sql》由会员分享，可在线阅读，更多相关《oracle按时间分组统计的sql（4页珍藏版）》请在金锄头文库上搜索。
1、Oracle按时间分组统计的sql如下表table1：日期(exportDate) 数量(amount)- -14-2月 -08 2010-3月 -08 214-4月 -08 614-6月 -08 7524-10月-09 2314-11月-09 4504-8月 -10 504-9月 -10 4404-10月-10 88注意：为了显示更直观，如下查询已皆按相应分组排序1.按年份分组select to_char(exportDate,yyyy),sum(amount) from table1 group by to_char(exportDate,yyyy);年份 数量-2009 682010 1
2、372008 1032.按月份分组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 202008-03 22008-04 62008-06 752009-10 232009-11 452010-08 52010-09 442010-10 883.按季度分组select to_char(exportDate,yyyy-Q),sum(amount) from
3、table1 group by to_char(exportDate,yyyy-Q)order by to_char(exportDate,yyyy-Q);季度 数量-2008-1 222008-2 812009-4 682010-3 492010-4 884.按周分组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 202008-11 22008-16 6
4、2008-24 752009-43 232009-46 452010-31 52010-35 442010-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 table1group by to_char(exportDate,yyyy);年份 第一季 第二季 第三季 第四季-2009 0 0 0 682010 0 0 49 882008 22 81 0 0
