如何打造“与众不同"的加入 MySQL
我有两个想要加入的 MySQL 表(产品和价格历史记录):
I have two MySQL tables (product and price history) that I would like to join:
产品表:
Id = int
Name = varchar
Manufacturer = varchar
UPC = varchar
Date_added = datetime
Price_h 表:
Id = int
Product_id = int
Price = int
Date = datetime
我可以执行一个简单的 LEFT JOIN:
I can perform a simple 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?
推荐答案
使用:
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
相关文章