group by是可以加条件的,因此按月好做。 group by substr(rq,1,6)就可以了,这里假设你的时间是yyyymmddhh24的了。。具体的自己改。按季度也是这个思路,但麻烦的是季度怎么算,条件比较杂,简单的办法就是两层group by select rq,count(*) from (select rq,count(*) from tablename group by substr(rq,1,6)) group by substr(rq,5,2) in ('01','02','03') or substr(rq,5,2) in ('04','05','06') or ..... 就是这个意思吧。
with t as (select '2013030415' a from dual union all select '2012110409' from dual) select substr(t.a, 1, 4) 年, substr(t.a, 5, 2) 月, to_char(to_date(t.a, 'yyyymmddhh24'), 'Q') 季度, count(1) cnt from t group by substr(t.a, 1, 4), substr(t.a, 5, 2), to_char(to_date(t.a, 'yyyymmddhh24'), 'Q');
group by substr(rq,1,6)就可以了,这里假设你的时间是yyyymmddhh24的了。。具体的自己改。按季度也是这个思路,但麻烦的是季度怎么算,条件比较杂,简单的办法就是两层group by
select rq,count(*) from
(select rq,count(*) from tablename group by substr(rq,1,6))
group by substr(rq,5,2) in ('01','02','03') or substr(rq,5,2) in ('04','05','06') or .....
就是这个意思吧。
这样统计,计算 都很方便,并且性能还好。
(select '2013030415' a
from dual
union all
select '2012110409' from dual)
select substr(t.a, 1, 4) 年,
substr(t.a, 5, 2) 月,
to_char(to_date(t.a, 'yyyymmddhh24'), 'Q') 季度,
count(1) cnt
from t
group by substr(t.a, 1, 4),
substr(t.a, 5, 2),
to_char(to_date(t.a, 'yyyymmddhh24'), 'Q');