MySQL 左连接 + 最小
看似简单的 MySQL 问题，但我以前从未这样做过..
Seemingly simple MySQL question, but I've never had to do this before..
我有两个表，项目和价格，具有一对多的关系.
I have two tables, items and prices, with a one-to-many relationship.
Items Table
id, name
Prices Table
id, item_id, price
哪里
prices.item_id = items.id
到目前为止我所拥有的:
What I have so far:
SELECT items.id, items.name, MIN(prices.price)
FROM items
LEFT JOIN prices ON items.id = prices.item_id
GROUP BY items.id
我如何还返回该最低价格的相应价格.id?谢谢！
How do I also return the corresponding prices.id for that minimum price? Thanks!
推荐答案
如果Items中有多个Prices记录且最低价格，这将返回多个记录:
This will return multiple records for a record in Items if there are multiple Prices records for it with the minimum price:
select items.id, items.name, prices.price, prices.id
from items
left join prices on (
items.id = prices.item_id
and prices.price = (
select min(price)
from prices
where item_id = items.id
)
);
相关文章