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

cqhunter 2006-12-11 06:17:08
我有这么一张表
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
...全文
548 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
Fooo 2006-12-15
  • 打赏
  • 举报
回复
又学了一回
cqhunter 2006-12-12
  • 打赏
  • 举报
回复
谢谢,结帖
shuqinpeng 2006-12-12
  • 打赏
  • 举报
回复
mark
yangyangxie 2006-12-12
  • 打赏
  • 举报
回复
又学了一回
dawugui 2006-12-12
  • 打赏
  • 举报
回复
如果时间段跨年怎么办,如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 行)

cqhunter 2006-12-11
  • 打赏
  • 举报
回复
set @cnt = month(@dt2) - month(@dt1) + 1
如果时间段跨年怎么办,如2005-10-1到2006-1-30?
dawugui 2006-12-11
  • 打赏
  • 举报
回复
你的查询时间应该为: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 行)
dawugui 2006-12-11
  • 打赏
  • 举报
回复
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 行)
中国风 2006-12-11
  • 打赏
  • 举报
回复
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 行)



22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧