本文介绍了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
)
);
这篇关于MySQL 左连接 + 最小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！