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

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)
go

select dt , count(1) cnt from
(
select 
    convert(varchar(7),dateadd(mm,num,z1),120) dt , z3
from tb,
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
    convert(varchar(7),dateadd(mm,num,z1),120)<=convert(varchar(7),z2,120)
) t
group by dt , z3

drop table tb

/*
dt      cnt         
------- ----------- 
2010-07 1
2010-08 1
2010-09 1
2010-07 1
2010-08 1
2010-09 1
2010-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 all
select '2010-07-22 14:57:20.313','2010-10-26 05:36:00.087',4

;with t
as(
select ROW_NUMBER()over(order by getdate()) as id,
* from tbl
),
m as(
select id,z1 from t
union all
select id,dateadd(month,1,a.z1) from m a
where not exists(select z2 from t b
where 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 m
group by  CONVERT(varchar(7),z1,120)
order by  CONVERT(varchar(7),z1,120)

/*
月份    次数
2010-07    2
2010-08    2
2010-09    2
2010-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 datetime
declare @en_dt datetime
select @st_dt = MIN(z1) from #表a
select @en_dt = MAX(z2) from #表a

declare @month_dt datetime
select @month_dt = @st_dt

---用循环分解数据到月
while DATEDIFF(MONTH,@en_dt,@month_dt)<=0
begin
insert 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_def
group by mon
/****
2010-07-01 00:00:00.000    2
2010-08-01 00:00:00.000    2
2010-09-01 00:00:00.000    2
2010-10-01 00:00:00.000    1
****/