22,210
社区成员
发帖
与我相关
我的任务
分享
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 行)
*/
declare @start datetime ,@end datetime
set @start='2008-11-01'
set @end='2009-3-1'
declare @tb table(date datetime)
while @start<=@end
begin
insert into @tb select @start
set @start=dateadd(mm,1,@start)
end
--select * from @tb ,tb2
select 时间=convert(char(7),s.date,120),name,销量=isnull((select count(*) from tb2 where addtime=s.date and name=t.name),0),
总量= isnull((select count(*) from tb2 where addtime=s.date ),0)
from @tb s,tb2 t
group by date,name
order by date desc
时间 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
declare @beginDate datetime
declare @endDate datetime
set @beginDate = '2008-11-01'
set @endDate = '2009-03-1'
create table tb(
ids int ,
[Name] nvarchar(20),
addtime datetime
)
insert into tb(ids,[name],[addtime])
select 1,'A','2009-01-01'
union all
select 2,'A','2009-01-01'
union all
select 3,'B','2009-03-01'
create table #temp(ids int identity(1,1), 日期 nvarchar(10))
declare @i int
set @i = 0
while @i <=datediff(MM,@beginDate,@endDate)
begin
insert into #temp
select convert(nvarchar(7), dateadd(MM,@i,@beginDate),120)
set @i = @i +1
end
select a.[name],b.日期,
[销量] = (select count(1) from vv where [name] = a.name and convert(nvarchar(7),addtime,120) = b.日期 ),(select count(1) from vv where convert(nvarchar(7),addtime,120) = b.日期 )'总量'
from vv a,#temp b
group by a.[name],b.日期
name 日期 销量 总量
-------------------- ---------- ----------- -----------
A 2008-11 0 0
A 2008-12 0 0
A 2009-01 2 2
A 2009-02 0 0
A 2009-03 0 1
B 2008-11 0 0
B 2008-12 0 0
B 2009-01 0 2
B 2009-02 0 0
B 2009-03 1 1
(10 行受影响)
declare @beginDate datetime
declare @endDate datetime
set @beginDate = '2008-11-01'
set @endDate = '2009-03-1'
create table tb(
ids int ,
[Name] nvarchar(20),
addtime datetime
)
insert into tb(ids,[name],[addtime])
select 1, 'A', '2009-01-01'
union all
select 2,'A', '2009-01-01'
union all
select 3,'B', '2009-03-01'
create table #temp(ids int identity(1,1), 日期 nvarchar(10))
declare @i int
set @i = 0
while @i <=datediff(MM,@beginDate,@endDate)
begin
insert into #temp
select convert(nvarchar(7), dateadd(MM,@i,@beginDate),120)
set @i = @i +1
end
select a.[name],b.日期,
[销量] = (select count(1) from tb where [name] = a.name and convert(nvarchar(7),addtime,120) = b.日期 )
from tb a,#temp b
group by a.[name],b.日期
name 日期 销量
-------------------- ---------- -----------
A 2008-11 0
A 2008-12 0
A 2009-01 2
A 2009-02 0
A 2009-03 0
B 2008-11 0
B 2008-12 0
B 2009-01 0
B 2009-02 0
B 2009-03 1
declare @t table(ID int, Name varchar(10) ,AddTime datetime)
insert @t select 1 , 'A', '2009-01-01'
insert @t select 2 , 'A', '2009-01-01'
insert @t select 3 , 'B', '2009-03-01'
declare @bt datetime,@et datetime
select @bt='2008-11-01',@et='2009-03-01'
select a.dt 时间,a.Name,销量=(select count(1) from @t where Name=a.Name and convert(varchar(7),AddTime,120)=convert(varchar(7),b.dt,120)),isnull(b.cnt,0) 总量
from
(
select t.name,dt=convert(varchar(7),dateadd(mm,number,@bt),120) from master..spt_values,(select distinct name from @t) t where type='p' and number<=datediff(mm,@bt,@et)
) a
left join
(
select name,dt=convert(varchar(7),AddTime,120),count(1) cnt from @t group by name,AddTime
) b
on a.dt=b.dt
order by a.dt desc,a.name
/*
时间 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
(10 行受影响)
*/
declare @start datetime ,@end datetime
set @start='2008-11-01'
set @end='2009-3-1'
declare @tb table(date datetime)
while @start<=@end
begin
insert into @tb select @start
set @start=dateadd(mm,1,@start)
end
--select * from @tb ,tb2
select 时间=s.date,name,销量=isnull((select count(*) from tb2 where addtime=s.date and name=t.name),0),
销量= isnull((select count(*) from tb2 where addtime=s.date and name=t.name ),0)
from @tb s,tb2 t
group by date,name
order by date desc
时间 name 销量 销量
2009-03-01 00:00:00.000 A 0 0
2009-03-01 00:00:00.000 B 1 1
2009-02-01 00:00:00.000 A 0 0
2009-02-01 00:00:00.000 B 0 0
2009-01-01 00:00:00.000 A 2 2
2009-01-01 00:00:00.000 B 0 0
2008-12-01 00:00:00.000 A 0 0
2008-12-01 00:00:00.000 B 0 0
2008-11-01 00:00:00.000 A 0 0
2008-11-01 00:00:00.000 B 0 0