求教Sql语句怎么写

[虫二二]

总体需求：查询当天前一个月的所有记录
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

[MootShao]

引用 1 楼 fredrickhu 的回复:

--功能：找出在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
------------------------------ -----------
2014-02-18                     0
2014-02-19                     0
2014-02-20                     0
2014-02-21                     0
2014-02-22                     0
2014-02-23                     0
2014-02-24                     0
2014-02-25                     0
2014-02-26                     0
2014-02-27                     0
2014-02-28                     998
2014-03-01                     998
2014-03-02                     998
2014-03-03                     998
2014-03-04                     1009
2014-03-05                     1009
2014-03-06                     1009
2014-03-07                     1119
2014-03-08                     1119
2014-03-09                     1119
2014-03-10                     1119
2014-03-11                     1119
2014-03-12                     1119
2014-03-13                     1119
2014-03-14                     1119
2014-03-15                     1119
2014-03-16                     1119
2014-03-17                     1119
2014-03-18                     1119

(29 row(s) affected)
*/

[--小F--]

--功能：找出在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