有从2007年到2008年的记录在一个表里面...
现在想统计当月每天在这个表的记录数... 如下
creationtime value
2007-08-01 1
2007-08-01 1
2008-07-01 1
2008-07-01 1
2008-07-04 1
2008-07-05 1那么期望得到的结果为
a b
2008-07-01 2
2008-07-02 0
2008-07-03 0
2008-07-04 1
2008-07-05 2
忘光了,想着就头痛!~~~~最近在写框架,不用SQL很多年~~~
现在想统计当月每天在这个表的记录数... 如下
creationtime value
2007-08-01 1
2007-08-01 1
2008-07-01 1
2008-07-01 1
2008-07-04 1
2008-07-05 1那么期望得到的结果为
a b
2008-07-01 2
2008-07-02 0
2008-07-03 0
2008-07-04 1
2008-07-05 2
忘光了,想着就头痛!~~~~最近在写框架,不用SQL很多年~~~
set @d='2008-7-1'select b.ds,sum(isnull(value,0)) as value
from (
select top 31 @d+(select count(*) from sysobjects where id<a.id) as ds
from sysobjects a
) b
left join tableA c on b.ds=c.creationtime
where convert(char(7),ds,21)=convert(char(7),@d,21)
group by b.ds
from table where year(creationtime)=year(getdate())
group by value
order by creationtime
create table tt(creationtime smalldatetime,value int)
create table result(creationtime smalldatetime,value int)insert into tt
select '2007-07-01',1
union all
select '2007-07-01',1
union all
select '2007-07-04',1
union all
select '2007-07-04',1
union all
select '2007-07-05',1
union all
select '2007-07-05',1insert into result
select creationtime,sum(value) from tt
group by creationtimedeclare @Min smalldatetime,@Max smalldatetime
select @Min=Min(creationtime),@Max=Max(creationtime) from ttwhile (@Min<=@Max)
Begin
if not exists(select 1 from result where datediff(day,creationtime,@Min)=0)insert into result
select @Min,0
set @Min=@Min+1
Endselect * from result order by creationtime
(date varchar(10),
Svalue int )
insert into @table
select '2007-07-01',1
union all
select '2007-07-01',1
union all
select '2007-07-04',1
union all
select '2007-07-04',1
union all
select '2007-07-05',1
union all
select '2007-07-05',1
declare @stratrdate varchar(10), @stopdate varchar(10),@i int
select @stratrdate='2007-07-01' ,@stopdate='2007-08-01',@i=0
declare @table1 table
(date varchar(10),
Svalue int )
while ( DATEADD(day, @i,@stratrdate)<=DATEADD(day, 0,@stopdate))
begin
insert into @table1
select ( DATEADD(day, @i,@stratrdate)),isnull((select sum(Svalue)from @table where date=( DATEADD(day, @i,@stratrdate)) group by date),0)
select @i=@i+1
end
select *from @table1