当前位置:网站首页 >> 法律

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

时间:2019-12-05 06:36:49 来源:互联网 阅读:0次

请教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")

乐至县中医医院
郴州市妇幼保健院怎么样
岳阳治性病好的医院
西宁专科癫痫病医院是哪家
江西治牛皮癣好的医院

相关文章

一周热门

热点排行

热门精选

友情链接:
媒体合作:

Copyright (c) 2011 八零CMS 版权所有 备案号:苏ICP备17012668号-3

网站地图