SQL表中获取时间段里每一天的数据，如果表里没有这一天的记录数据就为零
表   A
id     value     date    
1       aa         2007-01-02
2       bb         2007-01-01
3       cc         2007-01-04

现要得到在2007-01-02   到   2007-01-05的如下记录

value     date    
aa           2007-01-02
0             2007-01-03
cc           2007-01-04
o             2007-01-05

SQL查询语句能够完成吗，高手指点下，感激不尽~


------解决方案--------------------
select * from 表 where convert(varchar(8),date,112) between '20070102 'and ' '20070105 '
------解决方案--------------------
select * from 表 where convert(varchar(10),date,120) between '2007-01-02 'and ' '2007-01-05 '
或加小时段
select * from 表 where date between '2007-01-02 00:00:00 'and ' '2007-01-05 23:59:59 '
------解决方案--------------------

create table T(id int, value varchar(10), [date] datetime)
insert T select 1, 'aa ', '2007-01-02 '
union all select 2, 'bb ', '2007-01-01 '
union all select 3, 'cc ', '2007-01-04 '

select isnull(B.value, '0 ') as value, A.* from
(
select [Date]= '2007-01-02 '
union all select '2007-01-03 '
union all select '2007-01-04 '
union all select '2007-01-05 '
) as A
left join T as B on A.[Date]=B.[Date]

--result
value Date
---------- ----------
aa 2007-01-02
0 2007-01-03
cc 2007-01-04
0 2007-01-05

(4 row(s) affected)

------解决方案--------------------
@ta 为表变量生成一个月的日期表,与下面的语句一起用,就不用生成临时表
------解决方案--------------------
可以在表变量定义就行了
set @i= '2007-01-01 00:00:00 '--开始时间
while @i!> '2007-1-31 00:00:00 '--结束时间用getdate()系统时间