oracle数据库中分组查询语句,请教oracle按时间分组查询语句的写法

请教oracle按时间分组查询语句的写法？

最近由于要做报表，在一张表中有一个字段为date类型，现在想要在一段时间内(比如一年)能够按照时间段分组查询记录总和，比如我要能够查询2007年到2009年间按月份分组记录条数统计，网上提示用 group by to_char(date_column, 'yyy-mm'),构造如下sql语句：

select count(*), s.create_date as date_split

from sample s

where s.create_date between '2007-01' and '2009-12'

group by to_char(date_split, 'yyy-mm');

但是，我不只是要能按月份来分组，还要求能够按周和季度

季度的有个笨办法：

SQL> select to_char(b,'YYYY-MM') from table1;

TO_CHAR(B,'YYY

--------------

2009-09

2009-09

2009-09

2008-11

2008-11

2008-09

2009-05

2009-12

2009-12

已选择9行。

SQL> select  to_char(b,'YYYY'),sum(decode(to_char(b,'MM'),'01',1,'02',1,'03',1,0)) as one,

2    sum(decode(to_char(b,'MM'),'04',1,'05',1,'06',1,0)) as two,

3    sum(decode(to_char(b,'MM'),'07',1,'08',1,'09',1,0)) as three,

4    sum(decode(to_char(b,'MM'),'10',1,'11',1,'12',1,0)) as four from table1

5    group by to_char(b,'YYYY');

TO_CHAR(        ONE        TWO      THREE      FOUR

-------- ---------- ---------- ---------- ----------

2009              0          1          3          2

2008              0          0          1          2

按月份来分组

select to_char(date_column, 'yyyy-mm'),count(*)

from xxx

where date_column between '01-Jan-2007' and '31-Dec-2009'

group by to_char(date_column, 'yyyy-mm')

按季度来分组

select to_char(date_column, 'yyyy-Q'),count(*)

from xxx

where date_column between '01-Jan-2007' and '31-Dec-2009'

group by to_char(date_column, 'yyyy-Q')

按周来分组

select to_char(date_column, 'yyyy-IW'),count(*)

from xxx

where date_column between '01-Jan-2007' and '31-Dec-2009'

group by to_char(date_column, 'yyyy-IW')