sql - 根据订单日期从日期列表中选择价格

Question

我有一个包含成本价格的表和一个包含订单行的表

成本价格表:

Price_id, product_id, date, price
1,24,2018-08-06,100
2,24,2019-01-01,80
3,56,2018-11-11,500
4,57,2018-07-10,400
5,58,2017-01-01,500

订单表:

order_id, product_id, date, customer_id, qty, sales_price
1, 24, 2018-08-10, 344, 10, 250
2, 24, 2018-11-11, 538, 5, 250
3, 24, 2019-06-06, 678, 100, 250

我需要选择订单行日期的有效成本价。喜欢:

select
    ol.order_id,
    ol.product_id,
    ol.date,
    ol.customer_id,
    ol.qty,
    ----costprice @ orderdate ---,
    ol.sales_price

from orderlines as ol

left outer join Costprices as cp on cp.product_id = ol.product_id

我一直在寻找如何做到这一点，并且通常开始使用计算出的最大日期，这对于获取最新价格非常有用，但当您不需要最后一个价格时则不然，而是在特定日期更早的时候。

我觉得这可能很容易，但我无法理解

我正在使用 Postgres SQL 10 尝试在 tableplus 中编写 sql 查询

select
ol.order_id,
ol.product_id,
ol.date`
ol.customer_id,
ol.qty,
----costprice @ orderdate ---,
ol.sales_price

from orderlines as ol

let outer join Costprices as cp on cp.product_id = ol.product_id

我很期待

order_id, product_id, date, customer_id, qty, Costprice ,sales_price
1, 24, 2018-08-10, 344, 10, 100, 250
2, 24, 2018-11-11, 538, 5, 100, 250
3, 24, 2019-06-06, 678, 100, 80, 250

Answer 1

您可以使用横向连接:

select ol.*, cp.costprice
from orderlines ol left join lateral
     (select cp.*
      from costprices cp
      where cp.product_id = ol.product_id and
            cp.date <= ol.date
      order by cp.date desc
      limit 1
     ) cp
     on 1=1;

在您的情况下，您还可以使用相关子查询。基本上，如果您更喜欢这种方法，只需将 cp 移动到 select 子句中。