举一个例子 create table ta([date] datetime, [count] int) insert ta select '2007-1-1', 1 union all select '2007-1-2', 2 union all select '2007-1-4', 3 declare @start datetime,@end datetime select @start=min(date) from ta select @end=max(date) from ta declare @tmp table(date varchar(10)) while convert(varchar(10),@start,120)!>convert(varchar(10),@end,120) begin insert @tmp select convert(varchar(10),@start,120) set @start=@start+1 end select tb.date,[count]=isnull(count,0) from @tmp tb left join ta on tb.date=convert(varchar(10),ta.date,120)
create table ta([date] datetime, [count] int)
insert ta select '2007-1-1', 1
union all select '2007-1-2', 2
union all select '2007-1-4', 3
declare @start datetime,@end datetime
select @start=min(date) from ta
select @end=max(date) from ta
declare @tmp table(date varchar(10))
while convert(varchar(10),@start,120)!>convert(varchar(10),@end,120)
begin
insert @tmp select convert(varchar(10),@start,120)
set @start=@start+1
end
select tb.date,[count]=isnull(count,0)
from @tmp tb left join ta on tb.date=convert(varchar(10),ta.date,120)