表 a 字段,id,开始时间,结束时间,价格
1,2008-01-01,2008-09-10,220.0
2,2008-09-11,2008-09-30,280.0
3,2008-10-01,2008-10-10,320.0
3,2008-10-11,2008-12-31,350.0
输入一个开始时间,一个结束时间,返回这段时间内每天的价格,最好返回每天的日期和对应的价格
例输入开始时间2008-09-09,结束时间2008-09-13
返回
2008-09-09,220
2008-09-10,220
2008-09-11,280
2008-09-12,280
2008-09-13,280
方案一：采用存储过程的方案!
create table T([id] int,[开始时间] datetime,[结束时间] datetime,[价格] numeric(4,1))
insert into T
select 1,'2008-01-01','2008-09-10',220.0 union all
select 2,'2008-09-11','2008-09-30',280.0 union all
select 3,'2008-10-01','2008-10-10',320.0 union all
select 3,'2008-10-13','2008-12-31',350.0
select * from T
go
--Code
create procedure GetTimePrice(@begintime datetime,@endtime datetime)
as
declare @tmptime datetime
declare @tmp table (dt datetime,price numeric(4,1))--结果表
begin
set @tmptime=@begintime
while @tmptime<=@endtime
begin
insert into @tmp
select @tmptime,价格 from t where @tmptime between 开始时间 and 结束时间
set @tmptime=DATEADD(dd,1,@tmptime)
end
select * from @tmp
end
go
exec GetTimePrice '2008-09-09','2008-09-14'--执行
--Drop
drop table T
drop procedure GetTimePrice
方案二：采用Case方案
set nocount on
create table T([id] int,[开始时间] datetime,[结束时间] datetime,[价格] numeric(4,1))
insert into T
select 1,'2008-01-01','2008-09-10',220.0 union all
select 2,'2008-09-11','2008-09-30',280.0 union all
select 3,'2008-10-01','2008-10-10',320.0 union all
select 3,'2008-10-11','2008-12-31',350.0
declare @bgnTime datetime set @bgnTime = '2008-09-09'
declare @endTime datetime set @endTime = '2008-09-13'
select id
,case when [开始时间]<@bgnTime then @bgnTime else [开始时间] end as [开始时间] -- 让输出结果更贴近参数表现出来
,case when [结束时间]>@endTime then @endTime else [结束时间] end as [结束时间] -- 让输出结果更贴近参数表现出来
,[价格]
from T
where [开始时间]<@endTime
and [结束时间]>@bgnTime
-- id,开始时间,结束时间
-- 1,2008-09-09 00:00:00.000,2008-09-10 00:00:00.000
-- 2,2008-09-11 00:00:00.000,2008-09-13 00:00:00.000
drop table T
id 开始时间 结束时间 价格
----------- ------------------------------------------------------ ------------------------------------------------------ ------
1 2008-09-09 00:00:00.000 2008-09-10 00:00:00.000 220.0
2 2008-09-11 00:00:00.000 2008-09-13 00:00:00.000 280.0