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可能是一个起点，但我不确定...
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
面对这个问题，一个有效的方法是确保在连接时只选择每个日期对应的最新成本值。这可以通过使用窗口函数ROW_NUMBER()来实现，该函数可以帮助我们为每个产品ID和日期分配一个唯一的行号，基于cost_value的最新记录（通常根据start_date确定）。以下是一个改进后的查询示例：
WITH CostsRanked AS (
SELECT
T2.product_id,
T2.cost_component,
T2.cost_value,
T2.start_date,
T2.end_date,
ROW_NUMBER() OVER (PARTITION BY T2.product_id, T2.start_date
ORDER BY T2.start_date DESC) as rn
FROM costs T2
)
SELECT
T1.product_name,
T1.date,
T1.on_hand_inventory,
CR.cost_value
FROM inventory T1
INNER JOIN CostsRanked CR
ON T1.product_id = CR.product_id
AND T1.date BETWEEN CR.start_date AND CR.end_date
WHERE CR.rn = 1;
这个查询首先创建了一个名为CostsRanked的临时结果集，其中包含了每个产品ID及其成本组件、成本值等信息，并通过ROW_NUMBER()函数为每个产品ID和其不同的成本起始日期分配了行号，最新的成本记录会得到rn=1的标记（因为我们按start_date降序排列，最新的日期排在最前面）。
然后，主查询通过JOIN操作与这个临时结果集相连，但只选取那些在CostsRanked中rn=1的记录，即每个产品ID和日期对应的最新成本值，从而避免了重复的成本值问题。
