if object_id('[tb]') is not null drop table [tb] go create table [tb] ( [日期] varchar(9), [名称] varchar(9), [总数] int )declare @sdate datetime,@edate datetime; set @sdate='2009-03-31' set @edate='2009-04-02' declare @t table(tdate datetime) while @edate>=@sdate begin insert @t select @sdate set @sdate=dateadd(day,1,@sdate) endselect convert(varchar(10),a.tdate,120) as 日期, b.名称, isnull(b.总数,0) as 总数 from @t a left join tb b on a.tdate=b.日期/** 日期 名称 总数 ---------- --------- ----------- 2009-03-31 NULL 0 2009-04-01 NULL 0 2009-04-02 NULL 0(所影响的行数为 3 行) **/
参考一下这个例子吧,类似。 按月统计的问题? 数据结构 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 行) */
declare @beginDate datetime declare @endDate datetimeset @beginDate = '2008-11-01' set @endDate = '2009-03-1' create table tb_test(col1 varchar(10), col2 datetime, col3 int ) insert into tb_test(col3,col1,col2) select 1, 'A', '2009-01-01' union all select 1,'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 endselect a.col1,b.日期, [总数] = (select count(1) from tb where col1 = a.col1 and convert(nvarchar(7),addtime,120) = b.日期 )from tb_test a,#temp b group by a.col1,b.日期col1 日期 总数 ---------- ---------- ----------- A 2008-11 0 A 2008-12 0 A 2009-01 2 A 2009-02 0 A 2009-03 1 B 2008-11 0 B 2008-12 0 B 2009-01 2 B 2009-02 0 B 2009-03 1(10 row(s) affected)
create table tb(ID int, Name varchar(5), AddTime datetime) go declare @begindate datetime,@enddate datetime select @begindate='2009-03-31',@enddate='2009-04-02'select convert(varchar(10),AddTime,120) 日期,max(name) name,sum(num) 总数 from (select id num,name,AddTime from tb where convert(varchar(10),AddTime,120) between convert(varchar(10),@begindate,120) and convert(varchar(10),@enddate,120) union all select 0, '',dateadd(dd,b.number,@begindate) from master..spt_values b where b.type='p' and b.number between 0 and datediff(dd,@begindate,@enddate)) c group by convert(varchar(10),AddTime,120) order by convert(varchar(10),AddTime,120) drop table tb /* 日期 name 总数 ---------- ----- ----------- 2009-03-31 0 2009-04-01 0 2009-04-02 0 */
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
select col2 as 日期,col1 as 名称,isnull(sum(col3),0) as 总数 from table1 group by col2,col1
go
create table [tb]
(
[日期] varchar(9),
[名称] varchar(9),
[总数] int
)declare @sdate datetime,@edate datetime;
set @sdate='2009-03-31'
set @edate='2009-04-02'
declare @t table(tdate datetime)
while @edate>=@sdate
begin
insert @t select @sdate
set @sdate=dateadd(day,1,@sdate)
endselect
convert(varchar(10),a.tdate,120) as 日期,
b.名称,
isnull(b.总数,0) as 总数
from @t a
left join tb b on a.tdate=b.日期/**
日期 名称 总数
---------- --------- -----------
2009-03-31 NULL 0
2009-04-01 NULL 0
2009-04-02 NULL 0(所影响的行数为 3 行)
**/
按月统计的问题? 数据结构
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 行)
*/
declare @beginDate datetime
declare @endDate datetimeset @beginDate = '2008-11-01'
set @endDate = '2009-03-1'
create table tb_test(col1 varchar(10),
col2 datetime,
col3 int )
insert into tb_test(col3,col1,col2)
select 1, 'A', '2009-01-01'
union all
select 1,'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
endselect a.col1,b.日期,
[总数] = (select count(1) from tb where col1 = a.col1 and convert(nvarchar(7),addtime,120) = b.日期 )from tb_test a,#temp b
group by a.col1,b.日期col1 日期 总数
---------- ---------- -----------
A 2008-11 0
A 2008-12 0
A 2009-01 2
A 2009-02 0
A 2009-03 1
B 2008-11 0
B 2008-12 0
B 2009-01 2
B 2009-02 0
B 2009-03 1(10 row(s) affected)
go
declare @begindate datetime,@enddate datetime
select @begindate='2009-03-31',@enddate='2009-04-02'select convert(varchar(10),AddTime,120) 日期,max(name) name,sum(num) 总数 from
(select id num,name,AddTime from tb where convert(varchar(10),AddTime,120) between convert(varchar(10),@begindate,120) and convert(varchar(10),@enddate,120)
union all
select 0, '',dateadd(dd,b.number,@begindate) from master..spt_values b where b.type='p' and b.number between 0 and datediff(dd,@begindate,@enddate)) c
group by convert(varchar(10),AddTime,120) order by convert(varchar(10),AddTime,120)
drop table tb
/*
日期 name 总数
---------- ----- -----------
2009-03-31 0
2009-04-01 0
2009-04-02 0
*/
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