日期:2014-05-16 浏览次数:20654 次
--功能:找出在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
*/
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.