请问要实现如下目的，这样的SQL语句怎么写？

Question

--建立测试环境
Create Table 表(日期 datetime,雨量 varchar(10))
--插入数据
insert into 表
select '1960-1-2','7' union
select '1960-1-5','5' union
select '1960-1-9','12' union
select '1960-1-20','15' union
select '1960-2-1','0.7' union
select '1960-2-5','7' 
--测试语句
 declare @t table(日期 datetime)
 declare @date datetime
 set @date='1960-1-1'
 while @date<='1960-2-5' 
 begin
   insert into @t select @date
   set @date=@date+1
 end
  select a.日期,isnull(雨量,0)as 雨量  from @t a left join 表 b on a.日期=b.日期
 
--删除测试环境
Drop Table 表
/*日期                                                     雨量         
------------------------------------------------------ ---------- 
1960-01-01 00:00:00.000                                0
1960-01-02 00:00:00.000                                7
1960-01-03 00:00:00.000                                0
1960-01-04 00:00:00.000                                0
1960-01-05 00:00:00.000                                5
1960-01-06 00:00:00.000                                0
1960-01-07 00:00:00.000                                0
1960-01-08 00:00:00.000                                0
1960-01-09 00:00:00.000                                12
1960-01-10 00:00:00.000                                0
1960-01-11 00:00:00.000                                0
1960-01-12 00:00:00.000                                0
1960-01-13 00:00:00.000                                0
1960-01-14 00:00:00.000                                0
1960-01-15 00:00:00.000                                0
1960-01-16 00:00:00.000                                0
1960-01-17 00:00:00.000                                0
1960-01-18 00:00:00.000                                0
1960-01-19 00:00:00.000                                0
1960-01-20 00:00:00.000                                15
1960-01-21 00:00:00.000                                0
1960-01-22 00:00:00.000                                0
1960-01-23 00:00:00.000                                0
1960-01-24 00:00:00.000                                0
1960-01-25 00:00:00.000                                0
1960-01-26 00:00:00.000                                0
1960-01-27 00:00:00.000                                0
1960-01-28 00:00:00.000                                0
1960-01-29 00:00:00.000                                0
1960-01-30 00:00:00.000                                0
1960-01-31 00:00:00.000                                0
1960-02-01 00:00:00.000                                0.7
1960-02-02 00:00:00.000                                0
1960-02-03 00:00:00.000                                0
1960-02-04 00:00:00.000                                0
1960-02-05 00:00:00.000                                7
*/

Answer 1

真精彩，向 jinjazz(近身剪（N-P攻略）) 兄学习！

Answer 2

谢谢，但是在我的机上使用SQL查询分析器做这个测试需要3秒，这个速度是不是有点慢？请问，还有其它方法吗？

Answer 3

create function f_getday(
@begin datetime,@end datetime
)
returns @EveryDay table(NoLostday datetime)
as
  begin
  declare @TempT table (NoLostday datetime)
  declare @tempd datetime
  set @tempd=@begin
  --insert into @TempT select @tempd
  while datediff(d,@tempd,@end)>0
    begin
      insert into @TempT select @tempd
      set @tempd=dateadd(d,1,@tempd)
    end
  insert into @EveryDay Select * from @TempT
  return
  end
go
---1960-1-1至1960-2-5select convert(char(10),a.NoLostday,120) as 日期,isnull(b.雨量,0) as 雨量
from dbo.f_getday('1960-1-1','1960-2-5') a 
left join 表 b
on a.NoLostday=b.日期drop function f_getday