一个月份统计,求解决方法。高手来
一个月份统计,求解决办法。高手来。
表a
z1 z2 z3
2010-07-22 14:55:05.033 2010-09-15 18:48:12.000 3
2010-07-22 14:57:20.313 2010-10-26 05:36:00.087 4
....
需要按月统计出。
2010-07
2010-08
2010-09
2010-07
2010-08
2010-09
2010-10
.。。
------解决方案--------------------
表a
z1 z2 z3
2010-07-22 14:55:05.033 2010-09-15 18:48:12.000 3
2010-07-22 14:57:20.313 2010-10-26 05:36:00.087 4
....
需要按月统计出。
2010-07
2010-08
2010-09
2010-07
2010-08
2010-09
2010-10
.。。
------解决方案--------------------
- SQL code
select a_date,count(a_date) c_num from ( with t1 as( select date'2010-07-22' b_date,date'2010-09-15' e_date from dual union all select date'2010-07-22',date'2010-10-26' from dual ) select to_char(a_date,'yyyy-mm') a_date from ( SELECT ADD_MONTHS(DATE'2009-12-01',LEVEL) AS a_date FROM DUAL CONNECT BY LEVEL <= 12),t1 where a_date between ADD_MONTHS(b_date,-1) and e_date) group by a_date order by a_date a_date c_num -------------------------- 1 2010-07 2 2 2010-08 2 3 2010-09 2 4 2010-10 1