查询问题~!

[fs959]

数据库存放为：
2003-07-22 　300
2003-07-24 　500
2003-07-25 　810
2003-07-26　 706
2003-07-28　 605

像这样的数据￣！没有23、27日想让查出的数据为
2003-07-22 　300
2003-07-23 　0
2003-07-24 　500
2003-07-25 　810
2003-07-26　 706
2003-07-27 0
2003-07-28　 605
就是没有的加0

有没有高手可以写个存储过程来实现。输入一个日间段，把在时间段上没有的数据用0来显示来了!!
以上主要用于作线形图时按日期统计!

[愉快的登山者]

select * into #table from yourtable
declare @bdate datetime
declare @edate datetime
select @bdate = min(edate) from #table
select @edate = max(edate) from #table
while @bdate < @edate
begin
set @bdate = dateadd(day, 1, @bdate)
if not exists (select * from #table where edate = @bdate)
insert into #table select @bdate, 0
end
select * from #table order by edate


愉快的登山者

⊙
◢◣◢◣◢◣

[CrazyFor]

1,建序数表
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c


2,


declare @SD datetime,@ED datetime
set @Ed=getdate()
set @SD='2003-01-01'

select bb.Startdate+N-1，isnull(cc.值字段,0)
from numtab aa left join (select @SD as startDate) bb on bb.Startdate+N-1<@ED
left join 你的表 cc on bb.Startdate+N-1=cc.日期字段

[eastpond]

完全同意楼上的

[gzllich]

create procedure test
@date1 datetime,
@date2 datetime
as

declare @tmpdate datetime
declare table @tabdate(date1 datetime)
set @tmpdate = @date1
while convert(varchar(10), @tmpdate, 112) <= convert(varchar(10), @date2, 112)
begin
insert into @tabdate @tmpdate
set @tmpdate = @tmpdate + 1
end

select t1.date1, isnull(t2.num, 0)
from @tabdate t1, table1 t2
where t1.date1 *= t1.date1

[happydreamer]

create table #r (a datetime)

declare @i int,@j int
select @j=0,@i=datediff(day,min(时间),max(时间)) from yourtable


while(@j<=@i)
begin
insert into #r select dateadd(day,@j,min(时间) ) from yourtable
set @j=@j+1
end


select a.*,isnull(数据字段,0)
from #r a left join yourtable b
on a.a=b.a

[lynx1111]

CREATE TABLE [dbo].[TIME] (
[AA] [datetime] NULL ,
[BB] [int] NULL
)
-------------
INSERT .....
-------------

create table #r (a datetime)

declare @i int,@j int
select @j=0,@i=datediff(day,min(AA),max(AA)) from TIME

while(@j<=@i)
begin
insert into #r select dateadd(day,@j,min(AA) ) from TIME
set @j=@j+1
end

select a.*,isnull(BB,0)
from #r a left join TIME b
on a.a=b.AA