如何做出“与众不同"的提示加入MySQL [英] How to make a "distinct" join with MySQL
本文介绍了如何做出“与众不同"的提示加入MySQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想加入两个MySQL表(产品和价格历史记录):
I have two MySQL tables (product and price history) that I would like to join:
Product
表:
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
这篇关于如何做出“与众不同"的提示加入MySQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文