表就两个字段 金额和月份
表内容如下
price date
400 2009-6-1
100 2009-7-1
300 2009-8-1
我用select sum(price),month(date) from table
where date between '2009-6-1' and '2009-9-31'
group by month(date)结果是
400 6
100 7
300 8因表里没有9月份的数据 所以结果也没显示 我现在就想要显示成这样
400 6
100 7
300 8
0 9
就是根据我查询的条件是6月到9月 那么在6,7,8,9月中 如果哪个月没有数据 就显示成0
不知道实现不?? 谢谢大家了!!
表内容如下
price date
400 2009-6-1
100 2009-7-1
300 2009-8-1
我用select sum(price),month(date) from table
where date between '2009-6-1' and '2009-9-31'
group by month(date)结果是
400 6
100 7
300 8因表里没有9月份的数据 所以结果也没显示 我现在就想要显示成这样
400 6
100 7
300 8
0 9
就是根据我查询的条件是6月到9月 那么在6,7,8,9月中 如果哪个月没有数据 就显示成0
不知道实现不?? 谢谢大家了!!
select sum(price) as p,month(date) as m from table
where between '2009-6-1' and '2009-9-31'
group by month(date) )a right join
(select 1 as m union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union ) b
where a.m=b.m
create table s(price int,date datetime)
insert into s
select 400,'2009-6-1' union all
select 100,'2009-7-1' union all
select 300,'2009-8-1'
gocreate proc wsp
@star int,
@end int
as
create table #(id int)
while(@star<=@end)
begin
insert into # select @star
set @star=@star+1
end
select #.id,isnull(sum(price),0) from # left join s
on #.id=datepart(mm,date)
group by #.id
goexec wsp 6,9
insert into @tb
select 400 , '2009-06-01' union
select 100 , '2009-07-01'union
select 300 , '2009-08-01'
select isnull(a.p,0) as p,b.m from(
select sum(price) as p,month(date) as m from @tb
where date between '2009-06-01' and '2009-09-30'
group by month(date) )a right join
(select 1 as m union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 ) b
on a.m=b.m where b.m between 6 and 9
同时佩服剪剪的速度
按月统计的问题? 数据结构
ID Name AddTime
1 A 2009-01-01
2 A 2009-01-01 (注意这里只有1月和3月,但是下面的结果中,需要补上 2 月)
3 B 2009-03-01 需要的结果
时间上要求连续,例如用户搜索的时候是 2008-11-01 至 2009-03,哪么根据已有的数据,肯定是没有 2008-11、2008-12 这二个月的,这个时候就需要补上
时间 Name 销量 总量
2009-03 A 0 1
2009-03 B 1 1
2009-02 A 0 0
2009-02 B 0 0
2009-01 A 2 2
2009-01 B 0 2
2008-12 A 0 0
2008-12 B 0 0
2008-11 A 0 0
2008-11 B 0 0
-------------------------------------------------------------------------------------------------------
create table tb(ID int, Name varchar(5), AddTime datetime)
go
insert tb select 1, 'A' , '2009-01-01'
insert tb select 2, 'A' , '2009-01-01'
insert tb select 3, 'B' , '2009-03-01'
go
declare @begindate datetime,@enddate datetime
select @begindate='2008-11-01',@enddate='2009-03-01'select convert(varchar(7),AddTime,120) 时间,name,sum(num) 销量,sum(num) 总量 from
(select 1 num,name,AddTime from tb where convert(varchar(7),AddTime,120) between convert(varchar(7),@begindate,120) and convert(varchar(7),@enddate,120)
union all
select 0, a.name,dateadd(mm,b.number,@begindate) from (select distinct name from tb) a,master..spt_values b where b.type='p' and b.number between 0 and datediff(mm,@begindate,@enddate)) c
group by convert(varchar(7),AddTime,120) ,name order by convert(varchar(7),AddTime,120) desc ,name
/*
时间 name 销量 总量
------- ----- ----------- -----------
2009-03 A 0 0
2009-03 B 1 1
2009-02 A 0 0
2009-02 B 0 0
2009-01 A 2 2
2009-01 B 0 0
2008-12 A 0 0
2008-12 B 0 0
2008-11 A 0 0
2008-11 B 0 0(所影响的行数为 10 行)
*/
insert @tb select 400 ,'2009-6-1'
insert @tb select 100 ,'2009-7-1'
insert @tb select 300 ,'2009-8-1' declare @begindate datetime,@enddate datetime
select @begindate='2009-6-1',@enddate='2009-09-30'select price=sum(price),month([date]) from
(select price ,[date] from @tb where [date] between @begindate and @enddate
union all
select 0, dateadd(mm,b.number,@begindate) from master..spt_values b where b.type='p' and b.number between 0 and datediff(mm,@begindate,@enddate)) c
group by month([date])
order by month([date])
/*
price
----------- -----------
400 6
100 7
300 8
0 9(所影响的行数为 4 行)
*/
insert into @tb select 400,'2009-6-1'
union all select 100,'2009-7-1'
union all select 300,'2009-8-1'
select a.price,isnull(a.date,b.date+1) date from
(select sum(price)price,month(date)date from @tb
where date between '2009-6-1' and '2009-9-30'
group by month(date) ) a full join (select sum(price)price,month(date)date from @tb
where date between '2009-6-1' and '2009-9-30'
group by month(date) ) b on a.date=b.date+1
price date
----------- -----------
400 6
100 7
300 8
NULL 9(4 行受影响)