sql - 如何根据可能更改的日期范围选择表格中的值

Question

我似乎找不到专门解决我在这里尝试做的事情的要求的问题，但如果不是这种情况，我深表歉意。

我有一个示例表:

    type|start_date|end_date  |cost|

    bananas|2019-01-01|2019-01-31|100
    bananas|2019-02-01|2019-02-28|95
    juice  |null      |null      |55

等等。表格的重点是“食物”或其他任何东西，可以根据不同的时间范围有不同的价格，但是有一些项目，例如。果汁，当日期改变时不会受到影响(即它们保持不变)。

在另一个表中，我想根据上表中的日期范围查找一个值。

例如:

customerId|transaction_date|item   |quantity|

abc123    |2019-01-25      |bananas|4
abc126    |2019-02-06      |bananas|4
abc128    |2019-02-09      |juice  |1

因此，前两位顾客购买了香蕉，但一位是在一月份，一位是在二月份。然后另一个顾客买了果汁。如何根据我的其他表返回正确的对应成本。

我曾尝试在日期BETWEEN 两者的位置执行此操作，但这会疏远定价表中不存在的任何其他日期。我也尝试过使用 CASE 来解决这个问题，但无济于事。

最好的方法是什么？

我希望结果产生一个包含以下内容的连接表:

customerId|transaction_date|item   |quantity|cost|

abc123    |2019-01-25      |bananas|4       |100
abc126    |2019-02-06      |bananas|4       |95
abc128    |2019-02-09      |juice  |1       |55

cost 列按上述条件连接在哪里？

Answer 1

您可以在左连接上使用 ISNULL。

SELECT P.*, C.cost
FROM Purchases P 
LEFT JOIN Product_Costs C ON P.item = C.type 
      AND P.transaction_date >= ISNULL(C.start_date, P.transaction_date) 
      AND P.transaction_date <= ISNULL(C.end_date, P.transaction_date);

修改尼克的 fiddle :http://sqlfiddle.com/#!18/e366b/6