mysql-如何只使用一次最小值连接行

如果每个产品只有一行,即使有多个最低价格,也可以按 id 而不是 price 是的。

这看起来像

SELECT p.id, p.name, pr.price, pr.price_init
FROM products p LEFT JOIN
     prices pr
     ON pr.product_id = p.id AND
        pr.id = (SELECT pr2.id
                 FROM prices pr2
                 WHERE pr2.product_id = pr.product_id
                 ORDER BY pr2.price
                 LIMIT 1
                );

Here 是一个SQL小提琴。

注意在mysql 8+中,您将使用窗口函数:

SELECT p.id, p.name, pr.price, pr.price_init
FROM products p LEFT JOIN
     (SELECT pr.*,
             ROW_NUMBER() OVER (PARTITION BY pr.product_id ORDER BY pr.price) as seqnum
      FROM prices pr
     ) pr
     ON pr.product_id = p.id AND pr.seqnum = 1;