if object_id('pubs..tb') is not null
drop table tb
go
create table tb
(
noteid varchar(10),
saledate datetime
)
insert into tb(noteid,saledate) values('v1','2005-09-12')
insert into tb(noteid,saledate) values('v2','2005-09-12')
insert into tb(noteid,saledate) values('v3','2005-09-12')
insert into tb(noteid,saledate) values('v4','2005-08-12')
go
if object_id('pubs..mymonths') is not null
drop table mymonths
go
create table mymonths
(
mymonths varchar(7)
)
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2005-07-01' --开始时间
set @dt2 = '2006-10-01' --结束时间
declare @i as int
set @i = 1
declare @cnt as int --月数
set @cnt = (year(@dt2) - year(@dt1))*12 + (month(@dt2) - month(@dt1) + 1)
while @i <= @cnt
begin
insert into mymonths values(convert(varchar(7),@dt1,120))
set @dt1 = dateadd(month,1 , @dt1)
set @i = @i + 1
end
select m.mymonths as 月份 , isnull(n.数量,0) as 数量 from mymonths m
left join
(select convert(varchar(7),saledate,120) as saledate , count(*) as 数量 from tb group by convert(varchar(7),saledate,120)) n
on m.mymonths = n.saledate
drop table tb,mymonths
月份 数量
------- -----------
2005-07 0
2005-08 1
2005-09 3
2005-10 0
2005-11 0
2005-12 0
2006-01 0
2006-02 0
2006-03 0
2006-04 0
2006-05 0
2006-06 0
2006-07 0
2006-08 0
2006-09 0
2006-10 0
（所影响的行数为 16 行）