--或者且存储过程查询:
create proc p_qry
as
set nocount on
declare @dt1 datetime,@dt2 datetime,@sql varchar(8000)
select @dt1=min(日期),@dt2=max(日期) from 你的表
create table #t(id int identity(0,1),dt datetime)
set @sql='insert into #t select top '
+cast(datediff(day,@dt1,@dt2)+1 as varchar)
+' '''+convert(varchar(10),@dt1,120)
+''' from(select * from syscolumns) a,(select * from syscolumns) b'
exec(@sql)
update #t set dt=dateadd(day,id,dt)
select 日期=a.dt,值=isnull(b.值,'--')
from #t a join 你的表 b on a.dt=b.日期
set nocount off
go
--调用示例
select * from dbo.f_getdate('2003-01-01','2003-01-20')
*/
if exists(select 1 from sysobjects where id=object_id('f_getdate') and objectproperty(id,'IsInlineFunction')=0)
drop function f_getdate
go
create function f_getdate(@dt1 datetime,@dt2 datetime)
returns @re table(id int identity(0,1),dt datetime)
as
begin
declare @i int
select @i=datediff(day,@dt1,@dt2)+1
while @i>1
begin
insert into @re(dt) select top 100 @dt1 from syscolumns
set @i=@i-100
end
delete from @re where id>datediff(day,@dt1,@dt2)
update @re set dt=dateadd(day,id,dt)
return
end
go
--测试数据
declare @t table(日期 datetime,值 varchar(10))
insert into @t
select '2003-01-01','a'
union all select '2003-01-05','b'
--查询处理
declare @d1 datetime,@d2 datetime
select @d1=min(日期),@d2=max(日期) from @t
select 日期=a.dt,值=isnull(b.值,'----')
from dbo.f_getdate(@d1,@d2) a
left join @t b on a.dt=b.日期
/*--测试结果
日期 值
------------------------------------------------------ ----------
2003-01-01 00:00:00.000 a
2003-01-02 00:00:00.000 ----
2003-01-03 00:00:00.000 ----
2003-01-04 00:00:00.000 ----
2003-01-05 00:00:00.000 b
--调用示例
select * from dbo.f_getdate('2003-01-01','2003-01-20')
*/
if exists(select 1 from sysobjects where id=object_id('f_getdate') and objectproperty(id,'IsInlineFunction')=0)
drop function f_getdate
go
create function f_getdate(@dt1 datetime,@dt2 datetime)
returns @re table(id int identity(0,1),dt datetime)
as
begin
declare @i int
select @i=datediff(day,@dt1,@dt2)+1
while @i>1
begin
insert into @re(dt) select top 100 @dt1 from syscolumns
set @i=@i-100
end
delete from @re where id>datediff(day,@dt1,@dt2)
update @re set dt=dateadd(day,id,dt)
return
end
go
--调用实现你的要求
declare @d1 datetime,@d2 datetime
select @d1=min(日期),@d2=max(日期) from 表
select 日期=a.dt,值=isnull(b.值,'----')
from dbo.f_getdate(@d1,@d2) a
left join 表 b on a.dt=b.日期
declare @date1 datetime,@date2 datetime,@i int
set @date1='2003-11-2'
set @date2='2003-12-6'
set @i=0
create table #temp(日期 datetime)
while @i<= @date2-@date1
begin
insert #temp values(@date1 + @i)
set @i=@i+1
end
select a.日期,b.数据 from #temp a left join table1 b on a.日期 = b.日期