--下面建一辅助表,不必删除,以后会重复用到的。 create table t (d datetime) declare @d datetime set @d='2004-1-1' while @d<='2004-1-6' begin insert into t select @d set @d=@d+1 end--查询: select t.d,isnull(a.fld2,0) from t left join table1 a on t.d=a.fld1
create table table1(fld1 char(10),fld2 int) insert table1 select '2004-01-01', 1 union all select '2004-01-03', 3 union all select '2004-01-04', 4 union all select '2004-01-06', 6 select a.f1,isnull(b.fld2,0) from ( select convert(char(10),dateadd(day,aa.id,'2004-01-01'),120) as f1 from ( select 0 as id union all select 1 Union all select 2 Union all select 3 Union all select 4 Union all select 5 ) aa ) a left join table1 b on a.f1 = b.fld1 /* f1 ---------- ----------- 2004-01-01 1 2004-01-02 0 2004-01-03 3 2004-01-04 4 2004-01-05 0 2004-01-06 6(所影响的行数为 6 行)*/
通用的: declare @mindate datetime,@maxdate datetime,@sql nvarchar(4000) select @mindate=min(fld1),@maxdate=max(fld1) from table1 set @sql='select top '+cast(datediff(day,@mindate,@maxdate) as varchar) +' identity(int,1,1) id into ##tem from sysobjects a,sysobjects b,' +'sysobjects c,sysobjects d' exec sp_executesql @sql select fld1,fld2 from table1 union all select dateadd(day,id,@mindate) fld1,0 fld2 from ##tem where not exists(select 1 from table1 where dateadd(day,##tem.id,@mindate)=fld1) order by fld1 drop table ##tem
insert table1 select '2004-01-01', 1
union all select '2004-01-03', 3
union all select '2004-01-04', 4
union all select '2004-01-06', 6
select a.f1,isnull(b.fld2,0)
from (
select convert(char(10),dateadd(day,aa.id,'2004-01-01'),120) as f1
from (
select 0 as id
union all select 1
Union all select 2
Union all select 3
Union all select 4
Union all select 5
) aa
) a
left join table1 b on a.f1 = b.fld1
/*
f1
---------- -----------
2004-01-01 1
2004-01-02 0
2004-01-03 3
2004-01-04 4
2004-01-05 0
2004-01-06 6(所影响的行数为 6 行)*/
declare @mindate datetime,@maxdate datetime,@sql nvarchar(4000)
select @mindate=min(fld1),@maxdate=max(fld1) from table1
set @sql='select top '+cast(datediff(day,@mindate,@maxdate) as varchar)
+' identity(int,1,1) id into ##tem from sysobjects a,sysobjects b,'
+'sysobjects c,sysobjects d'
exec sp_executesql @sql
select fld1,fld2 from table1
union all
select dateadd(day,id,@mindate) fld1,0 fld2
from ##tem where not exists(select 1 from table1 where dateadd(day,##tem.id,@mindate)=fld1)
order by fld1
drop table ##tem