有这样一组数据:
A B C
A 2011-01-15 60
A 2011-1-20 1460
A 2011-1-24 1100
如何用最佳获得如下时间列数据
A 2011-01-15 60
A 2011-01-16 60
A 2011-01-17 60
A 2011-01-18 60
A 2011-01-19 60
A 2011-01-20 1460
A 2011-01-21 1460
A 2011-01-22 1460
A 2011-01-23 1460
A 2011-01-24 1100
A B C
A 2011-01-15 60
A 2011-1-20 1460
A 2011-1-24 1100
如何用最佳获得如下时间列数据
A 2011-01-15 60
A 2011-01-16 60
A 2011-01-17 60
A 2011-01-18 60
A 2011-01-19 60
A 2011-01-20 1460
A 2011-01-21 1460
A 2011-01-22 1460
A 2011-01-23 1460
A 2011-01-24 1100
--我吭一下
select lie from tt where lie like 'A 20%'
我觉得这样功能就实现了
参考下这个帖子吧
declare @sdate datetime
declare @edate datetime
set @sdate = '2009-8-30'
set @edate = '2009-9-5'
select
dateadd(dd,num,@sdate)
from
(select isnull((select count(1) from sysobjects where id <t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,@sdate) <=@edate
/*
------------------------------------------------------
2009-08-30 00:00:00.000
2009-08-31 00:00:00.000
2009-09-01 00:00:00.000
2009-09-02 00:00:00.000
2009-09-03 00:00:00.000
2009-09-04 00:00:00.000
2009-09-05 00:00:00.000
(所影响的行数为 7 行)
*/
--功能:找出在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)
from
master..spt_values
where
datediff(day,dateadd(day,number,@startdate), @enddate)>=0
and number>0
and type='p'
/*----------
2009-08-31
2009-09-01
2009-09-02
2009-09-03
2009-09-04
2009-09-05
(6 行受影响)
/*
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/09/24/4587448.aspx
create table #tb(a varchar(10),B DATETIME ,c int)
insert into #tb
select 'A','2011-01-15',60
union all
select 'A','2011-01-20',1460
union all
select 'A','2011-01-24',1110
drop table #temp
declare @a varchar(10),@b datetime,@c int,@n int =1,@d datetime
declare @tb table(a varchar(10),b datetime, c int)
select *,ROW_NUMBER() over(order by getdate()) rowid into #temp from #tb
while exists(select a from #temp where rowid=@n)
begin
select @a=a,@b=B,@c=c from #temp where rowid=@n
insert into @tb
select @a,@b,@c
set @d=@b+1
while @d<(select B from #temp where rowid=@n+1)
begin
insert into @tb
select @a,@d,@c
set @d=@d+1
end
set @n=@n+1
end
select * from @tb
a b c
---------- ----------------------- -----------
A 2011-01-15 00:00:00.000 60
A 2011-01-16 00:00:00.000 60
A 2011-01-17 00:00:00.000 60
A 2011-01-18 00:00:00.000 60
A 2011-01-19 00:00:00.000 60
A 2011-01-20 00:00:00.000 1460
A 2011-01-21 00:00:00.000 1460
A 2011-01-22 00:00:00.000 1460
A 2011-01-23 00:00:00.000 1460
A 2011-01-24 00:00:00.000 1110
--小F姐说了要写出来!必须写出来!如下:--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (A varchar(1),B varchar(10),C int)
insert into [tb]
select 'A','2011-01-15',60 union all
select 'A','2011-1-20',1460 union all
select 'A','2011-1-24',1100--开始查询
declare @startdate datetime=(select MIN(B) from tb)
declare @enddate datetime=(select Max(B) from tb);with cte as(
select rid=ROW_NUMBER() over (order by getdate()),
dt=dateadd(day,number,@startdate)
from master..spt_values
where type='p' and number>=0
and datediff(day,dateadd(day,number,@startdate),@enddate)>=0
),
ctf as(
select * from cte a left join tb b on(a.dt=convert(datetime,b.B))
)
--select * from ctf
select A=isnull(A,'A'),
B=convert(varchar(10),dt,120),
C=ISNULL(C,(select top 1 C from ctf where [C] is not null and rid<a.rid order by [C] desc))
from ctf a/*
A B C
---- ---------- -----------
A 2011-01-15 60
A 2011-01-16 60
A 2011-01-17 60
A 2011-01-18 60
A 2011-01-19 60
A 2011-01-20 1460
A 2011-01-21 1460
A 2011-01-22 1460
A 2011-01-23 1460
A 2011-01-24 1100(10 行受影响)
后面的按你的需求来即可,如上。
insert into [tb]
select 'A','2011-01-15',60 union all
select 'A','2011-1-20',1460 union all
select 'A','2011-1-24',1100 union all
select 'B','2011-2-12',230 union all
select 'B','2015-2-25',540B不出来啦!