mysql - sql 根据最新价格求和

Question

需要准备销售元素的年度报告，所以准备了一份fiddle为此...我有每个项目的价格表...所以当获取项目的价格时我需要price_dt的最新价格...一开始认为很简单..但花费的时间比我应该的要多...:(

报告针对的是每年销售的商品总数

price table
'I001', '2014-03-31', '200.00'
'I002', '2014-03-31', '200.00'  //old price
'I002', '2014-05-31', '150.00'  //latest price
'I003', '2015-03-31', '200.00'
'I004', '2015-07-31', '120.00'
'I005', '2015-07-31', '180.00'


items
'I001', '2014-06-30', 'SOLD'
'I002', '2014-06-30', 'SOLD'
'I003', '2015-06-30', 'SOLD'
'I004', '2015-08-30', 'SOLD'
'I005', '2015-08-30', 'UNSOLD'


I001 & I002 are sold in 2014 ('SOLD' status in items table)
I003 & I004 are sold in 2015 (but I005 is not sold yet in year 2015)

I002的价格是150(这是最新价格)

当我计算每件商品的价格时，我需要考虑最新价格

所以我的最终输出应该是

2014     350
2015     320

Answer 1

您需要再次加入价格表，最大日期为

select
year(i.sell_date),
sum(p.price) from items i
left join price p ON i.item_id = p.item_id
join (
  select item_id,max(price_dt) as price_dt from price
  group by item_id
)x on x.price_dt = p.price_dt and x.item_id = p.item_id
where i.status = 'SOLD' 
group by year(i.sell_date);