表 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'--執(zhí)行
--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 [開始時間] -- 讓輸出結果更貼近參數(shù)表現(xiàn)出來
,case when [結束時間]>@endTime then @endTime else [結束時間] end as [結束時間] -- 讓輸出結果更貼近參數(shù)表現(xiàn)出來
,[價格]
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