create proc pro_test create table #table(rq datetime) declare @rq datetime set @rq='2003-05-01' while @rq< dateadd(mm,1,'2003-05-01') begin insert into #table valuse(@rq) set @rq=dateadd(dy,1,@rq) end select rq,isnull(sum(qty),0) from @table left join table on dt=rq group by rq
create proc 名 @开始时间 datetime, @结束时间 datetime as select top 365 identity(int,0,1) id into #aa from sysobjects,sysobjects aselect tem.dt,isnull(表.qty,0) qty from 表 right join (select dateadd(@开始时间,day,id) dt from #aa where dateadd(@开始时间,day,id)<=@结束时间) tem on 表.dt=tem.dt go
declare @a table (dt datetime,qty int) insert into @a values('2003-5-2', 10) insert into @a values('2003-5-2', 15) insert into @a values('2003-5-5', 20) insert into @a values('2003-5-5', 8) insert into @a values('2003-5-6', 50) select * from @adeclare @start datetime,@end datetime select @start=(select min(dt) from @a) select @end=(select max(dt) from @a) declare @temp datetime create table #datetable(adate datetime) select @temp=@start while @temp<=@end begin insert into #datetable select @temp select @temp=dateadd(d,1,@start) end select * from #datetable select a.adate as dt,sum(isnull(b.qty,0)) as qty from #datetable a left join @a b on a.adate=b.dt group by a.adatedrop table #datetable
create proc 名 @开始时间 datetime, @结束时间 datetime as select top 365 identity(int,0,1) id into #aa from sysobjects,sysobjects aselect tem.dt,isnull(表.qty,0) qty from 表 right join (select dateadd(day,id,@开始时间) dt from #aa where dateadd(day,id,@开始时间)<=@结束时间) tem on 表.dt=tem.dt go
declare @a table (dt datetime,qty int) insert into @a values('2003-5-2', 10) insert into @a values('2003-5-2', 15) insert into @a values('2003-5-5', 20) insert into @a values('2003-5-5', 8) insert into @a values('2003-5-6', 50) declare @start datetime,@end datetime select @start=(select min(dt) from @a) select @end=(select max(dt) from @a) declare @temp datetime create table #datetable(adate datetime) select @temp=@start while @temp<=@end begin insert into #datetable select @temp select @temp=dateadd(d,1,@temp) end select a.adate as dt,sum(isnull(b.qty,0)) as qty from #datetable a left join @a b on a.adate=b.dt group by a.adatedrop table #datetable
--版本1 create proc 名 @开始时间 datetime, @结束时间 datetime as select top 365 identity(int,0,1) id into #aa from sysobjects,sysobjects aselect tem.dt,isnull(sum(表.qty),0) qty from 表 right join (select dateadd(day,id,@开始时间) dt from #aa where dateadd(day,id,@开始时间)<=@结束时间) tem on 表.dt=tem.dt group by dt go --版本2 ,求本月 create proc 名 @月份 int as select top 32 identity(int,0,1) id into #aa from sysobjects,sysobjects aselect tem.dt,isnull(sum(表.qty),0) qty from 表 right join (select dateadd(day,id,datename(yy,getdate())+'-'+cast(@月份 as char(2))+'-1') dt from #aa where dateadd(day,id,datename(yy,getdate())+'-'+cast(@月份 as char(2))+'-1')<=(select max(时间) from 表 where datediff(month,时间,getdate())=0 )) tem on 表.dt=tem.dt group by 表.qty go
Declare @date DateTime Select * into #a From table where 1=2 Set @Date = '2003/5/1'While @Date <'2003/6/1' Begin Insert Into #a Select @date,Sum(IsNull(Qnty,0)) From table Where Dt = @date Select @Date = @Date + 1 End
declare @dt datetime declare @mdt datetime select @mdt=min(dt) from a (表) decalre mycursor cursor locate for select dt from a open mycursor fetch next from mycursor into @dt while fetch_status=o begin set @mdt=@dt while @mdt<@dt begin Insert a(dt,qty) values(@mdt,0) @mdt=DateAdd(dd,1,@begindate) end fetch next from mycursor into @dt
end select dt,sum(isnull(qty,0)) from a group by dt close
Create Procedure Sp_MonthSum @strMon Char(6) --如'200305' As Select Top 31 DayNo=Identity(tinyint,0,1) Into #Day From sysobjects Declare @FirstDay smalldatetime,@LastDay smalldatetime Select @FirstDay = convert(smalldatetime,@strMon+'01',112) Select @LastDay = DateAdd(Month,1,@FirstDay)-1Select A.dt,sum(isnull(b.qty,0)) From (Select DateAdd(Day,DayNo,@FirstDay) dt From #Day Where DayNo<(@LastDay-@FirstDay)) A left join YourTabel B on A.dt = B.dt Group By A.dt Order by A.dt
忘了说清楚,dt是datetime型,有时、分、秒
把这个copy到你的查询分析器里试一下就明白了 declare @a table (dt datetime,qty int) insert into @a values('2003-5-2', 10) insert into @a values('2003-5-2', 15) insert into @a values('2003-5-5', 20) insert into @a values('2003-5-5', 8) insert into @a values('2003-5-6', 50)declare @start datetime,@end datetime select @start=(select min(dt) from @a) select @end=(select max(dt) from @a) declare @temp datetime create table #datetable(adate datetime) select @temp=@start while @temp<=@end begin insert into #datetable select @temp select @temp=dateadd(d,1,@temp) end select a.adate as dt,sum(isnull(b.qty,0)) as qty from #datetable a left join @a b on a.adate=b.dt group by a.adatedrop table #datetable
create proc ppp ( @begindate datetime, @enddate int) as while (@enddate >0) begin insert into #t(d) values datedadd(d, @begindate ,@enddate ) end select d, qty from a,#t where #t.d*= a.dt
create table #table(rq datetime)
declare @rq datetime
set @rq='2003-05-01'
while @rq< dateadd(mm,1,'2003-05-01')
begin
insert into #table valuse(@rq)
set @rq=dateadd(dy,1,@rq)
end
select rq,isnull(sum(qty),0)
from @table left join table on dt=rq
group by rq
@开始时间 datetime,
@结束时间 datetime
as
select top 365 identity(int,0,1) id into #aa from sysobjects,sysobjects aselect tem.dt,isnull(表.qty,0) qty from 表 right join
(select dateadd(@开始时间,day,id) dt from #aa where dateadd(@开始时间,day,id)<=@结束时间) tem
on 表.dt=tem.dt
go
insert into @a values('2003-5-2', 10)
insert into @a values('2003-5-2', 15)
insert into @a values('2003-5-5', 20)
insert into @a values('2003-5-5', 8)
insert into @a values('2003-5-6', 50)
select * from @adeclare @start datetime,@end datetime
select @start=(select min(dt) from @a)
select @end=(select max(dt) from @a)
declare @temp datetime
create table #datetable(adate datetime)
select @temp=@start
while @temp<=@end
begin
insert into #datetable select @temp
select @temp=dateadd(d,1,@start)
end
select * from #datetable
select a.adate as dt,sum(isnull(b.qty,0)) as qty from #datetable a left join @a b
on a.adate=b.dt
group by a.adatedrop table #datetable
@开始时间 datetime,
@结束时间 datetime
as
select top 365 identity(int,0,1) id into #aa from sysobjects,sysobjects aselect tem.dt,isnull(表.qty,0) qty from 表 right join
(select dateadd(day,id,@开始时间) dt from #aa where dateadd(day,id,@开始时间)<=@结束时间) tem
on 表.dt=tem.dt
go
insert into @a values('2003-5-2', 10)
insert into @a values('2003-5-2', 15)
insert into @a values('2003-5-5', 20)
insert into @a values('2003-5-5', 8)
insert into @a values('2003-5-6', 50)
declare @start datetime,@end datetime
select @start=(select min(dt) from @a)
select @end=(select max(dt) from @a)
declare @temp datetime
create table #datetable(adate datetime)
select @temp=@start
while @temp<=@end
begin
insert into #datetable select @temp
select @temp=dateadd(d,1,@temp)
end
select a.adate as dt,sum(isnull(b.qty,0)) as qty from #datetable a left join @a b
on a.adate=b.dt
group by a.adatedrop table #datetable
create proc 名
@开始时间 datetime,
@结束时间 datetime
as
select top 365 identity(int,0,1) id into #aa from sysobjects,sysobjects aselect tem.dt,isnull(sum(表.qty),0) qty from 表 right join
(select dateadd(day,id,@开始时间) dt from #aa where dateadd(day,id,@开始时间)<=@结束时间) tem
on 表.dt=tem.dt group by dt
go
--版本2 ,求本月
create proc 名
@月份 int
as
select top 32 identity(int,0,1) id into #aa from sysobjects,sysobjects aselect tem.dt,isnull(sum(表.qty),0) qty from 表 right join
(select dateadd(day,id,datename(yy,getdate())+'-'+cast(@月份 as char(2))+'-1') dt from #aa where dateadd(day,id,datename(yy,getdate())+'-'+cast(@月份 as char(2))+'-1')<=(select max(时间) from 表 where datediff(month,时间,getdate())=0 )) tem
on 表.dt=tem.dt group by 表.qty
go
Select * into #a From table where 1=2
Set @Date = '2003/5/1'While @Date <'2003/6/1'
Begin
Insert Into #a
Select @date,Sum(IsNull(Qnty,0))
From table
Where Dt = @date Select @Date = @Date + 1
End
declare @mdt datetime
select @mdt=min(dt) from a (表)
decalre mycursor cursor locate
for
select dt from a
open mycursor
fetch next from mycursor into @dt
while fetch_status=o
begin
set @mdt=@dt
while @mdt<@dt
begin
Insert a(dt,qty) values(@mdt,0)
@mdt=DateAdd(dd,1,@begindate)
end
fetch next from mycursor into @dt
end
select dt,sum(isnull(qty,0)) from a
group by dt
close
@strMon Char(6) --如'200305'
As
Select Top 31 DayNo=Identity(tinyint,0,1) Into #Day From sysobjects
Declare @FirstDay smalldatetime,@LastDay smalldatetime
Select @FirstDay = convert(smalldatetime,@strMon+'01',112)
Select @LastDay = DateAdd(Month,1,@FirstDay)-1Select A.dt,sum(isnull(b.qty,0)) From
(Select DateAdd(Day,DayNo,@FirstDay) dt
From #Day Where DayNo<(@LastDay-@FirstDay)) A
left join YourTabel B on A.dt = B.dt
Group By A.dt
Order by A.dt
declare @a table (dt datetime,qty int)
insert into @a values('2003-5-2', 10)
insert into @a values('2003-5-2', 15)
insert into @a values('2003-5-5', 20)
insert into @a values('2003-5-5', 8)
insert into @a values('2003-5-6', 50)declare @start datetime,@end datetime
select @start=(select min(dt) from @a)
select @end=(select max(dt) from @a)
declare @temp datetime
create table #datetable(adate datetime)
select @temp=@start
while @temp<=@end
begin
insert into #datetable select @temp
select @temp=dateadd(d,1,@temp)
end
select a.adate as dt,sum(isnull(b.qty,0)) as qty from #datetable a left join @a b
on a.adate=b.dt
group by a.adatedrop table #datetable
( @begindate datetime,
@enddate int)
as
while (@enddate >0)
begin
insert into #t(d)
values
datedadd(d, @begindate ,@enddate )
end
select d, qty
from a,#t
where #t.d*= a.dt