有如下一組資料
ItemCode TransDate Price
LC0998 4/1/2020 5.3
LC0998 6/5/2020 6.7
LC0998 6/28/2020 6.2
LC0998 7/9/2020 7.8
LC9876 4/1/2020 2.3
LC9876 5/7/2020 12.4
LC9876 6/20/2020 11.7
LC9876 7/5/2020 15.65
假如日期是 5/25/2020,那么 LC0998取5.3, LC9876取12.4
假如日期是7/5/2020,那么 LC0998取6.2, LC9876取15.65
請問改SQL陳述句要怎么寫？
uj5u.com熱心網友回復：
create table #t(ItemCode varchar(10),TransDate varchar(10),Price decimal(10,2))
insert into #t(ItemCode,TransDate,Price)
select 'LC0998','4/1/2020',5.3 union all
select 'LC0998','6/5/2020',6.7 union all
select 'LC0998','6/28/2020',6.2 union all
select 'LC0998','7/9/2020',7.8 union all
select 'LC9876','4/1/2020',2.3 union all
select 'LC9876','5/7/2020',12.4 union all
select 'LC9876','6/20/2020',11.7 union all
select 'LC9876','7/5/2020',15.65
--假如日期是 5/25/2020,那么 LC0998取5.3, LC9876取12.4
declare @fdate date
select @fdate='5/25/2020'
select ItemCode,Price
from (select ItemCode,TransDate,Price,rn=row_number() over(partition by ItemCode order by cast(TransDate as date) desc)
from #t
where cast(TransDate as date)<=@fdate) t
where t.rn=1
/*
ItemCode Price
---------- ---------------------------------------
LC0998 5.30
LC9876 12.40
(2 行受影響)
*/
--假如日期是7/5/2020,那么 LC0998取6.2, LC9876取15.65
declare @fdate date
select @fdate='7/5/2020'
select ItemCode,Price
from (select ItemCode,TransDate,Price,rn=row_number() over(partition by ItemCode order by cast(TransDate as date) desc)
from #t
where cast(TransDate as date)<=@fdate) t
where t.rn=1
/*
ItemCode Price
---------- ---------------------------------------
LC0998 6.20
LC9876 15.65
(2 行受影響)
*/
uj5u.com熱心網友回復：
謝謝你的回復， 不好意思，我的問題沒有描述得完整有如下二組資料
表 PriceList
ItemCode TransDate Price
LC0998 4/1/2020 5.3
LC0998 6/5/2020 6.7
LC0998 6/28/2020 6.2
LC0998 7/9/2020 7.8
LC9876 4/1/2020 2.3
LC9876 5/7/2020 12.4
LC9876 6/20/2020 11.7
LC9876 7/5/2020 15.65
另一表 ItemList
ItemCode CreatedDate
LC9876 5/1/2020
LC9876 6/21/2020
LC9876 7/9/2020
LC9876 7/10/2020
LC0998 5/2/2020
LC0998 7/10/2020
得到結果：
ItemCode CreatedDate Price
LC9876 5/1/2020 2.3
LC9876 6/21/2020 11.7
LC9876 7/9/2020 15.65
LC9876 7/10/2020 15.65
LC0998 5/2/2020 5.3
LC0998 7/10/2020 7.8
要求：
根據ItemList表找出PirceList表中日期最近的Price 值,
uj5u.com熱心網友回復：
create table PriceList(ItemCode varchar(10),TransDate varchar(10),Price decimal(10,2))
insert into PriceList(ItemCode,TransDate,Price)
select 'LC0998','4/1/2020',5.3 union all
select 'LC0998','6/5/2020',6.7 union all
select 'LC0998','6/28/2020',6.2 union all
select 'LC0998','7/9/2020',7.8 union all
select 'LC9876','4/1/2020',2.3 union all
select 'LC9876','5/7/2020',12.4 union all
select 'LC9876','6/20/2020',11.7 union all
select 'LC9876','7/5/2020',15.65
create table ItemList(ItemCode varchar(10),CreatedDate varchar(10))
insert into ItemList(ItemCode,CreatedDate)
select 'LC9876','5/1/2020' union all
select 'LC9876','6/21/2020' union all
select 'LC9876','7/9/2020' union all
select 'LC9876','7/10/2020' union all
select 'LC0998','5/2/2020' union all
select 'LC0998','7/10/2020'
select a.ItemCode,a.CreatedDate,Price=t.Price
from ItemList a
outer apply
(select top 1 x.Price
from PriceList x
where cast(x.TransDate as date)<=cast(a.CreatedDate as date)
and x.ItemCode=a.ItemCode
order by cast(x.TransDate as date) desc) t
/*
ItemCode CreatedDate Price
---------- ----------- --------------
LC9876 5/1/2020 2.30
LC9876 6/21/2020 11.70
LC9876 7/9/2020 15.65
LC9876 7/10/2020 15.65
LC0998 5/2/2020 5.30
LC0998 7/10/2020 7.80
(6 行受影響)
*/
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/17869.html
標籤：疑難問題