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

2021-09-06 来源：临清租房网

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

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

select count(*), eate_date as date_split

from sample s

where eate_date between \'\' and \'\'

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

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

季度的有个笨办法：

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

TO_CHAR(B,\'YYY

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

已选择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\')