一个月份统计，求解决方法。高手来

一个月份统计，求解决办法。高手来。
表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
.。。


结果


2010-07 2
2010-08 2
2010-09 2
2010-10 1

... 


[解决办法]

SQL code

create table tb(z1 datetime,z2 datetime,z3 int)insert into tb values('2010-07-22 14:55:05.033', '2010-09-15 18:48:12.000', 3)insert into tb values('2010-07-22 14:57:20.313', '2010-10-26 05:36:00.087', 4)goselect dt , count(1) cnt from(select     convert(varchar(7),dateadd(mm,num,z1),120) dt , z3from tb,    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) awhere    convert(varchar(7),dateadd(mm,num,z1),120)<=convert(varchar(7),z2,120)) tgroup by dt , z3drop table tb/*dt      cnt         ------- ----------- 2010-07 12010-08 12010-09 12010-07 12010-08 12010-09 12010-10 1（所影响的行数为 7 行）*/
[解决办法]
SQL code
--> 测试数据：[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([z1] datetime,[z2] datetime,[z3] int)insert [tbl]select '2010-07-22 14:55:05.033','2010-09-15 18:48:12.000',3 union allselect '2010-07-22 14:57:20.313','2010-10-26 05:36:00.087',4;with tas(select ROW_NUMBER()over(order by getdate()) as id,* from tbl),m as(select id,z1 from tunion allselect id,dateadd(month,1,a.z1) from m awhere not exists(select z2 from t bwhere b.z2=DATEADD(MONTH,1,a.z1))and month(a.z1)<(select MONTH(z2) from t where t.id=a.id))select CONVERT(varchar(7),z1,120) as 月份,COUNT(*) as 次数 from mgroup by  CONVERT(varchar(7),z1,120)order by  CONVERT(varchar(7),z1,120)/*月份    次数2010-07    22010-08    22010-09    22010-10    1*/
[解决办法]
SQL code
----创建测试数据表create table #表a(z1 datetime,z2 datetime,z3 int)----建立测试数据insert into #表a(z1,z2,z3) values('2010-07-22 14:55:05.033','2010-09-15 18:48:12.000',3)insert into #表a(z1,z2,z3) values('2010-07-22 14:57:20.313','2010-10-26 05:36:00.087',4)----select * from #表a---建立月分解数据表create table #month_def(mon datetime,z1 datetime,z2 datetime,z3 int)---设置查询时间段declare @st_dt datetimedeclare @en_dt datetimeselect @st_dt = MIN(z1) from #表aselect @en_dt = MAX(z2) from #表adeclare @month_dt datetimeselect @month_dt = @st_dt---用循环分解数据到月while DATEDIFF(MONTH,@en_dt,@month_dt)<=0begininsert into #month_def(mon,z1,z2,z3)select cast((cast(DATEPART(year,@month_dt) as char(4)) +'-'+ cast(DATEPART(MONTH,@month_dt)as CHAR(2)) + '-01') as datetime),    z1,z2,z3     from #表a    where DATEDIFF(MONTH,z1,@month_dt)>=0    and DATEDIFF(MONTH,z2,@month_dt)<=0    set @month_dt = dateadd(month,1,@month_dt)end    ---select * from #month_def----统计合计结果select mon,sum(c_n) from (select mon,z3/(DATEDIFF(MONTH,z1,z2)+1) as c_n from #month_def) month_defgroup by mon/****2010-07-01 00:00:00.000    22010-08-01 00:00:00.000    22010-09-01 00:00:00.000    22010-10-01 00:00:00.000    1****/