/* Limpire: */--原始数据:@Test declare @Test table(名称 varchar(5),值 decimal(3,1),日期 datetime) insert @Test select 'aaaaa',13,'2007-08-01' union all select 'aaaaa',12.6,'2007-08-03' union all select 'bbbb',25,'2007-08-02' union all select 'bbbb',32,'2007-08-03' union all select 'bbbb',37,'2007-08-05' union all select 'ccccc',22,'2007-08-02' union all select 'ccccc',31,'2007-08-03' union all select 'ccccc',20,'2007-08-05'declare @minDate datetime set @minDate = '2007-08-01'select top 5 iniDate=identity(int,39293,1) into #iniDate from syscolumns,sysobjects --select datediff(day,@minDate,max(日期))+1 from @Test --> top 5 --select datediff(day,0,@minDate) --> 39293select a.名称,值=isnull(b.值,0),a.日期 from ( select a.名称,日期=cast(b.iniDate as datetime) from (select 名称,maxDate=max(日期) from @Test group by 名称) a join #iniDate b on cast(b.iniDate as datetime) between @minDate and a.maxDate ) a left join @Test b on a.名称=b.名称 and a.日期=b.日期/* 名称 值 日期 aaaaa 13.0 2007-08-01 00:00:00.000 aaaaa .0 2007-08-02 00:00:00.000 aaaaa 12.6 2007-08-03 00:00:00.000 bbbb .0 2007-08-01 00:00:00.000 bbbb 25.0 2007-08-02 00:00:00.000 bbbb 32.0 2007-08-03 00:00:00.000 bbbb .0 2007-08-04 00:00:00.000 bbbb 37.0 2007-08-05 00:00:00.000 ccccc .0 2007-08-01 00:00:00.000 ccccc 22.0 2007-08-02 00:00:00.000 ccccc 31.0 2007-08-03 00:00:00.000 ccccc .0 2007-08-04 00:00:00.000 ccccc 20.0 2007-08-05 00:00:00.000 */drop table #iniDate
格式即:名称 值 日期
aaa 0 2007-08-01
aaa 0 2007-08-02
...
....
bbb 0 2007-08-01
bbb 0 2007-08-02
在连接这两个表就OK了
aaaaa 0 2007-08-02
aaaaa 12.6 2007-08-03
.....
bbbb 0 2007-08-01
bbbb 25 2007-08-02
bbbb 32 2007-08-03
bbbb 0 2007-08-04
bbbb 37 2007-08-05
......
ccccc 0 2007-08-01
ccccc 22 2007-08-02
ccccc 31 2007-08-03
ccccc 0 2007-08-04
ccccc 20 2007-08-05
Limpire:
*/--原始数据:@Test
declare @Test table(名称 varchar(5),值 decimal(3,1),日期 datetime)
insert @Test
select 'aaaaa',13,'2007-08-01' union all
select 'aaaaa',12.6,'2007-08-03' union all
select 'bbbb',25,'2007-08-02' union all
select 'bbbb',32,'2007-08-03' union all
select 'bbbb',37,'2007-08-05' union all
select 'ccccc',22,'2007-08-02' union all
select 'ccccc',31,'2007-08-03' union all
select 'ccccc',20,'2007-08-05'declare @minDate datetime
set @minDate = '2007-08-01'select top 5 iniDate=identity(int,39293,1) into #iniDate from syscolumns,sysobjects
--select datediff(day,@minDate,max(日期))+1 from @Test --> top 5
--select datediff(day,0,@minDate) --> 39293select a.名称,值=isnull(b.值,0),a.日期
from
(
select a.名称,日期=cast(b.iniDate as datetime)
from
(select 名称,maxDate=max(日期) from @Test group by 名称) a
join #iniDate b on cast(b.iniDate as datetime) between @minDate and a.maxDate
) a
left join @Test b
on a.名称=b.名称 and a.日期=b.日期/*
名称 值 日期
aaaaa 13.0 2007-08-01 00:00:00.000
aaaaa .0 2007-08-02 00:00:00.000
aaaaa 12.6 2007-08-03 00:00:00.000
bbbb .0 2007-08-01 00:00:00.000
bbbb 25.0 2007-08-02 00:00:00.000
bbbb 32.0 2007-08-03 00:00:00.000
bbbb .0 2007-08-04 00:00:00.000
bbbb 37.0 2007-08-05 00:00:00.000
ccccc .0 2007-08-01 00:00:00.000
ccccc 22.0 2007-08-02 00:00:00.000
ccccc 31.0 2007-08-03 00:00:00.000
ccccc .0 2007-08-04 00:00:00.000
ccccc 20.0 2007-08-05 00:00:00.000
*/drop table #iniDate