如何与 MySQL 进行“不同的”连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3088686/
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
How to make a "distinct" join with MySQL
提问by Steven Potter
I have two MySQL tables (product and price history) that I would like to join:
我有两个要加入的 MySQL 表（产品和价格历史记录）：
Producttable:
Product桌子：
Id = int
Name = varchar
Manufacturer = varchar
UPC = varchar
Date_added = datetime
Price_htable:
Price_h桌子：
Id = int
Product_id = int
Price = int
Date = datetime
I can perform a simple LEFT JOIN:
我可以执行一个简单的 LEFT JOIN：
SELECT Product.UPC, Product.Name, Price_h.Price, Price_h.Date
FROM Product
LEFT JOIN Price_h
ON Product.Id = Price_h.Product_id;
But as expected if I have more than one entry for a product in the price history table, I get one result for each historical price.
但正如预期的那样，如果我在价格历史记录表中有多个产品条目，我会为每个历史价格得到一个结果。
How can a structure a join that will only return one instance of each produce with only the newest entry from the price history table joined to it?
如何构建一个连接，只返回每个产品的一个实例，并且只有价格历史表中的最新条目连接到它？
回答by OMG Ponies
Use:
用：
SELECT p.upc,
p.name,
ph.price,
ph.date
FROM PRODUCT p
LEFT JOIN PRICE_H ph ON ph.product_id = p.id
JOIN (SELECT a.product_id,
MAX(a.date) AS max_date
FROM PRICE_H a
GROUP BY a.product_id) x ON x.product_id = ph.product_id
AND x.max_date = ph.date
回答by a1ex07
SELECT Product.UPC, Product.Name, Price_h.Price, Price_h.Date
FROM Product
LEFT JOIN Price_h
ON (Product.Id = Price_h.Product_id AND Price_h.Date =
(SELECT MAX(Date) FROM Price_h ph1 WHERE ph1.Product_id = Product.Id));
回答by Justin Ethier
Try this:
尝试这个：
SELECT Product.UPC, Product.Name, Price_h.Price, MAX(Price_h.Date)
FROM Product
INNER JOIN Price_h
ON Product.Id = Price_h.Product_id
GROUP BY Product.UPC, Product.Name, Price_h.Price
回答by vlnik
SELECT n.product_id,
n.product_name,
n.product_articul,
n.product_price,
n.product_discount,
n.product_description,
n.product_care,
(SELECT photo_name FROM siamm_product_photos WHERE product_id = n.product_id LIMIT 1) AS photo_name
FROM siamm_product as n;
回答by Henry
Why not keep it simple and fast:
为什么不保持简单和快速：
SELECT
Product.UPC, Product.Name, Price_h.Price, Price_h.Date
FROM
Product
LEFT JOIN
Price_h
ON Product.Id = Price_h.Product_id;
ORDER BY
Price_h.Date DESC
LIMIT 1
