表 a 字段,id,開始時(shí)間,結(jié)束時(shí)間,價(jià)格
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
輸入一個(gè)開始時(shí)間,一個(gè)結(jié)束時(shí)間,返回這段時(shí)間內(nèi)每天的價(jià)格,最好返回每天的日期和對應(yīng)的價(jià)格
例輸入開始時(shí)間2008-09-09,結(jié)束時(shí)間2008-09-13
返回
2008-09-09,220
2008-09-10,220
2008-09-11,280
2008-09-12,280
2008-09-13,280
方案一：采用存儲(chǔ)過程的方案!
create table T([id] int,[開始時(shí)間] datetime,[結(jié)束時(shí)間] datetime,[價(jià)格] 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))--結(jié)果表
begin
set @tmptime=@begintime
while @tmptime<=@endtime
begin
insert into @tmp
select @tmptime,價(jià)格 from t where @tmptime between 開始時(shí)間 and 結(jié)束時(shí)間
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,[開始時(shí)間] datetime,[結(jié)束時(shí)間] datetime,[價(jià)格] 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 [開始時(shí)間]<@bgnTime then @bgnTime else [開始時(shí)間] end as [開始時(shí)間] -- 讓輸出結(jié)果更貼近參數(shù)表現(xiàn)出來
,case when [結(jié)束時(shí)間]>@endTime then @endTime else [結(jié)束時(shí)間] end as [結(jié)束時(shí)間] -- 讓輸出結(jié)果更貼近參數(shù)表現(xiàn)出來
,[價(jià)格]
from T
where [開始時(shí)間]<@endTime
and [結(jié)束時(shí)間]>@bgnTime
-- id,開始時(shí)間,結(jié)束時(shí)間
-- 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 開始時(shí)間 結(jié)束時(shí)間 價(jià)格
----------- ------------------------------------------------------ ------------------------------------------------------ ------
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