I've got the following 2 tables:
我有以下2个表格：
- ingredients (id, name)
- 成分（id，名称）
- ingredient_prices (id, ingredient_id, price, created_at)
- ingredient_prices（id，ingredient_id，price，created_at）
such that one ingredient can have many prices. What will be the best way to get the latest entered price per ingredient? I know it can be done easily with sub-selects, but I want to know if there is a more efficient way for doing it.
这样一种成分可以有很多价格。获得每种成分最新输入价格的最佳方法是什么？我知道可以通过子选择轻松完成，但我想知道是否有更有效的方法。
1 个解决方案
#1
4
I do it this way:
我是这样做的：
SELECT i.*, p1.*
FROM ingredients i
JOIN ingredient_prices p1 ON (i.id = p1.ingredient_id)
LEFT OUTER JOIN ingredient_prices p2 ON (i.id = p2.ingredient_id
AND p1.created_at < p2.created_at)
WHERE p2.id IS NULL;
This searches for a price (p2) that is more recent than p1, and if none is found, then p1 must be the most recent price.
这将搜索比p1更新的价格（p2），如果没有找到，则p1必须是最近的价格。
If there is a possibility of more than one price on a given date, then you need another term to filter out the duplicate.
如果在给定日期可能有多个价格，那么您需要另一个术语来过滤掉副本。
#1
4
I do it this way:
我是这样做的：
SELECT i.*, p1.*
FROM ingredients i
JOIN ingredient_prices p1 ON (i.id = p1.ingredient_id)
LEFT OUTER JOIN ingredient_prices p2 ON (i.id = p2.ingredient_id
AND p1.created_at < p2.created_at)
WHERE p2.id IS NULL;
This searches for a price (p2) that is more recent than p1, and if none is found, then p1 must be the most recent price.
这将搜索比p1更新的价格（p2），如果没有找到，则p1必须是最近的价格。
If there is a possibility of more than one price on a given date, then you need another term to filter out the duplicate.
如果在给定日期可能有多个价格，那么您需要另一个术语来过滤掉副本。