mysql - 根据 DATETIME 类型从表中获取最新记录

Question

你好 StackOverflow 社区，

我有两张 table

products
=======
id
name

prices
======
id
product_id
price
date_added

“date_added”列是 DATETIME 类型。

我的问题是如何选择 products.name、prices.price、prices.date_added，其中 date_added 最大。

结果集应包含每个产品 1 行，其中包含价格和最大添加日期。

如果这还不够理解，请告诉我!

这是一组记录

INSERT INTO `prices` (`id`, `product_id`, `price`, `date_added`) VALUES
(1, 1, 1.23, '2011-11-16 13:18:14'),
(2, 1, 1.25, '2011-11-17 13:18:24'),
(3, 2, 1.5, '2011-11-15 13:18:32'),
(4, 3, 1.89, '2011-11-15 13:18:41'),
(5, 4, 0.98, '2011-11-14 13:18:57'),
(6, 5, 1.87, '2011-11-17 13:19:29'),
(7, 5, 1.98, '2011-11-16 13:19:44'),
(8, 3, 1.87, '2011-11-13 13:19:52'),
(9, 2, 1.45, '2011-11-05 13:20:01'),
(10, 1, 2.34, '2011-11-16 13:20:10'),
(11, 4, 2.87, '2011-11-18 13:49:21'),
(12, 5, 1.64, '2011-11-19 13:49:33');

INSERT INTO `products` (`id`, `name`) VALUES
(1, 'Karamhtros'),
(2, 'Tralalas'),
(3, 'Giwrgos'),
(4, 'Sotiris'),
(5, 'Tassos');

Answer 1

试试这个

SELECT p.name, pr.price, pr.date_added
FROM products p INNER JOIN prices pr
  ON p.id = pr.product_id
GROUP BY p.id
HAVING pr.date_added = MAX(pr.date_added)

在用户评论后编辑:
试试这个

SELECT p.id, p.name, pr.price, pr.date_added
FROM products p INNER JOIN prices pr
  ON p.id = pr.product_id
INNER JOIN (SELECT product_id, MAX(date_added) AS da 
            FROM prices GROUP BY product_id) pr2
  ON pr.product_id = pr2.product_id AND pr.date_added = pr2.da