MySQL 左连接 + 最小 [英] MySQL Left Join + Min

问题描述

看似简单的 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 左连接 + 最小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！