按月统计的问题，即数据中没有该月的数据，该月的数据显示为0

[cqhunter]

我有这么一张表
noteid saledate

v1 2005-09-12
v2 2005-09-12
v3 2005-09-12
v4 2005-08-12

的一张表，我想统计这张表中指定时间段内的每月数量，数量是一条记录一个数量的 ，如2005-7-1到2005-10-1内实现按月统计

表现结果我想是这样

月份 数量
2005-09 3
2005-08 1
2005-07 0

[中国风]

declare @ta table( noteid varchar(5), saledate datetime)
insert @ta
select 'v1' , '2005-09-12' union all
select 'v2', '2005-09-12' union all
select 'v3', '2005-09-12' union all
select 'v4', '2005-08-12'

select 月份=convert(varchar(7),saledate,120),记录=count(*)
from @ta group by convert(varchar(7),saledate,120)

（所影响的行数为 4 行）

月份 记录
------- -----------
2005-08 1
2005-09 3

（所影响的行数为 2 行）

[dawugui]

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 = '2005-10-01' --结束时间
declare @i as int
set @i = 1
declare @cnt as int --月数
set @cnt = 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

（所影响的行数为 4 行）

[dawugui]

你的查询时间应该为:2005-7-1到2005-09-30

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 = '2005-09-30' --结束时间
declare @i as int
set @i = 1
declare @cnt as int --月数
set @cnt = 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

（所影响的行数为 3 行）

[cqhunter]

set @cnt = month(@dt2) - month(@dt1) + 1
如果时间段跨年怎么办，如2005-10-1到2006-1-30？

[dawugui]

如果时间段跨年怎么办，如2005-10-1到2006-1-30？


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 行）

[yangyangxie]

又学了一回

[shuqinpeng]

mark

[cqhunter]

谢谢，结帖

[Fooo]

又学了一回