MySQL 左连接 + 最小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7588142/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Left Join + Min
提问by Charles
Seemingly simple MySQL question, but I've never had to do this before..
看似简单的 MySQL 问题，但我以前从未这样做过。
I have two tables, items and prices, with a one-to-many relationship.
我有两个表，项目和价格，具有一对多的关系。
Items Table
id, name
Prices Table
id, item_id, price
Where
在哪里
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
How do I also return the corresponding prices.id for that minimum price? Thanks!
我如何还为该最低价格返回相应的价格。谢谢！
回答by Patrick Finnegan
This will return multiple records for a record in Items if there are multiple Prices records for it with the minimum price:
如果有多个具有最低价格的 Prices 记录，这将返回 Items 中记录的多个记录：
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
)
);
回答by Sonny
New, working answer, based on the final example in the MySQL 5.0 Reference Manual - 3.6.4. The Rows Holding the Group-wise Maximum of a Certain Column:
新的有效答案，基于 MySQL 5.0 参考手册 - 3.6.4 中的最后一个示例。包含某个列的分组最大值的行：
SELECT items.id, items.name, prices.price, prices.id
FROM items
LEFT JOIN prices
ON prices.item_id = items.id
LEFT JOIN prices AS filter
ON filter.item_id = prices.item_id
AND filter.price < prices.price
WHERE filter.id IS NULL
The LEFT JOINworks on the basis that when prices.priceis at its minimum value, there is no filter.pricewith a smaller value and the filterrows values will be NULL.
其LEFT JOIN工作原理是，当prices.price处于最小值时，没有filter.price较小的值并且filter行值将为 NULL。
Original incorrect answer:
原错误答案：
SELECT items.id, items.name, prices.price, prices.id
FROM items
LEFT JOIN prices ON prices.item_id = items.id
ORDER BY prices.price ASC
LIMIT 1
回答by Eric R.
SELECT top 1 items.id, items.name, prices.price, prices.id
FROM items
LEFT JOIN prices ON items.id = prices.item_id
ORDER BY prices.price ASC
回答by JMK
Ok, how about?
好的，怎么样？
SELECT items.id, items.name, MIN(prices.price), prices.id
FROM items
LEFT JOIN prices ON items.id = prices.item_id
GROUP BY items.id, MIN(prices.price)
