SQL-提高标准：在阈值内进行查询

考虑以下两个表-一个盘点清单：

+------------+--------------+----------+-------------------+ | product_id | product_name | date | on_hand_inventory | +------------+--------------+----------+-------------------+ | 1 | Product A | 6/1/2019 | 37 | | 1 | Product A | 6/2/2019 | 36 | | 1 | Product A | 6/3/2019 | 35 | | 1 | Product A | 6/4/2019 | 40 | | 1 | Product A | 6/5/2019 | 42 | +------------+--------------+----------+-------------------+

...以及其他跟踪费用：

+------------+----------------+------------+------------+------------+ | product_id | cost_component | cost_value | start_date | end_date | +------------+----------------+------------+------------+------------+ | 1 | FOB | 15 | 1/1/2019 | 12/31/9999 | | 1 | FOB | 15.5 | 6/2/2019 | 12/31/9999 | | 1 | FOB | 16 | 6/4/2019 | 12/31/9999 | +------------+----------------+------------+------------+------------+

因此，事实证明，该表costs.end_date已维护不善，并且包含许多重复的结束日期。以下查询：

SELECT T1.product_name,
       T1.date,
       T1.on_hand_inventory,
       T2.cost_value
  FROM inventory T1
 INNER JOIN costs T2
         ON T1.product_id = T2.product_id
        AND T1.date BETWEEN T2.start_date AND T2.end_date

产生以下结果：

+------------+--------------+----------+-------------------+----------------+-------+ | product_id | product_name | date | on_hand_inventory | cost_component | value | +------------+--------------+----------+-------------------+----------------+-------+ | 1 | Product A | 6/1/2019 | 37 | FOB | 15 | | 1 | Product A | 6/1/2019 | 37 | FOB | 15.5 | | 1 | Product A | 6/1/2019 | 37 | FOB | 16 | | 1 | Product A | 6/2/2019 | 36 | FOB | 15.5 | | 1 | Product A | 6/2/2019 | 36 | FOB | 16 | | 1 | Product A | 6/3/2019 | 35 | FOB | 15.5 | | 1 | Product A | 6/3/2019 | 35 | FOB | 16 | | 1 | Product A | 6/4/2019 | 40 | FOB | 16 | | 1 | Product A | 6/5/2019 | 42 | FOB | 16 | +------------+--------------+----------+-------------------+----------------+-------+

注意几个日期的重复成本值。

我觉得APPLY可能是一个起点，但我不确定...