总体需求：查询当天前一个月的所有记录
eg：
比如今天是 2014-03-18，我查询前一个月的所有记录数据，时间应该是 2014-02-18 - 2014-03-18
但数据库里面不是每天的数据记录都有，这时就要延用之前的数据
CreateTime Price
2014-02-28 998
2014-03-04 1009
2014-03-07 1119
那查询当天一个月的数据出来就是
2014-02-18 0
..
..
2014-02-28 998
2014-03-01 998
2014-03-02 998
...
...
2014-03-04 1009
------解决方案--------------------
--功能：找出在2个日期之间的日期
--startdate:2009年9月15日 endDate:2009年10月3日
declare @startdate datetime,@enddate datetime
set @startdate='2009-08-30'
set @enddate='2009-09-05'
select convert(varchar(10),dateadd(day,number,@startdate),120) AS [date]
from
master..spt_values
where
datediff(day,dateadd(day,number,@startdate), @enddate)>=0
and number>=0
and type='p'
/*date
----------
2009-08-30
2009-08-31
2009-09-01
2009-09-02
2009-09-03
2009-09-04
2009-09-05
*/
构造这个数据以后和你的表LEFT JOIN
------解决方案--------------------
create table #kong (a int,b varchar(10))可以参考这个
insert into #kong
select 1,'as'
union all select 2,NULL
union all select 3,'asda'
union all select 4,NULL
union all select 5,NULL
union all select 6,'zxc'
union all select 7,NULL
union all select 8,NULL
union all select 9,'asd'
union all select 10,NULL
select f.a,isnull(f.b,ff.b)
from (
select a.a,a.b,a.a-MIN(a.a-isnull(b.a,a.a))[c]
from #kong a
left join (select a from #kong where b is not null) b
on a.a>b.a group by a.a,a.b
) f join #kong ff on ff.a=f.c
order by f.a
drop table #kong
------解决方案--------------------
create table ch
(CreateTime varchar(20),Price int)
insert into ch
select '2014-02-28',998 union all
select '2014-03-04',1009 union all
select '2014-03-07',1119
select a.CreateTime,
isnull(b.Price,
isnull((select top 1 Price
from ch c
where c.CreateTime<=a.CreateTime order by c.CreateTime desc),0)) 'Price'
from
(select convert(varchar,dateadd(d,-1*number,getdate()),23) 'CreateTime'
from master.dbo.spt_values
where type='P' and number<=datediff(d,
convert(varchar,dateadd(m,-1,getdate()),23),
convert(varchar,getdate(),23))) a
left join ch b on a.CreateTime=b.CreateTime
order by a.CreateTime
/*
CreateTime Price