如下表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')
order by to_char(exportDate,'yyyy');
年份????? 数量
-----------------------------
2008??? 103
2009??? 68
2010??? 137
?
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
?
?
?
?
?