Oracle按不同时间分组统计的sql

如下表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