mysql - 如何连接表，同时限制第三个表中 2 个最新行的数据？

Question

如何选择所有产品及其类别名称，并且每种产品仅包含最新 2 个价格？

产品

id | product_name | category_id | created_at

价格

id | price | product_id | created_at

类别

id | category_name

这是我的方法，它连接所有数据。如果我将 LIMIT 2 放在第二个 INNER JOIN 上，我只会得到两行。如何获取所有产品，但每种产品只有两个最新价格？

SELECT
    products.product_name,
    categories.category_name,
    prices.price
FROM
    products
INNER JOIN categories ON products.category_id = categories.id
INNER JOIN (
    SELECT
        prices.price,
    prices.product_id
    FROM
        prices
    ORDER BY
        created_at DESC
) as prices 
ON prices.product_id = products.id
GROUP BY products.product_name, categories.category_name, prices.price

此数据可用于生成表并为其播种

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

INSERT INTO `categories` VALUES ('1', 'fruits');
INSERT INTO `categories` VALUES ('2', 'vegetables');
INSERT INTO `categories` VALUES ('3', 'seeds');

DROP TABLE IF EXISTS `prices`;
CREATE TABLE `prices` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `price` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;

INSERT INTO `prices` VALUES ('1', '2', '1', '2017-01-07 04:03:45');
INSERT INTO `prices` VALUES ('2', '3', '2', '2017-01-07 04:03:45');
INSERT INTO `prices` VALUES ('3', '4', '3', '2017-01-07 04:03:45');
INSERT INTO `prices` VALUES ('4', '2', '4', '2017-01-07 04:03:45');
INSERT INTO `prices` VALUES ('5', '5', '1', '2017-02-07 04:03:45');
INSERT INTO `prices` VALUES ('6', '6', '2', '2017-02-07 04:03:45');
INSERT INTO `prices` VALUES ('7', '3', '3', '2017-02-07 04:03:45');
INSERT INTO `prices` VALUES ('8', '2', '4', '2017-02-07 04:03:45');
INSERT INTO `prices` VALUES ('9', '3', '1', '2017-03-10 06:03:45');
INSERT INTO `prices` VALUES ('10', '6', '2', '2017-03-10 06:03:45');
INSERT INTO `prices` VALUES ('11', '7', '3', '2017-03-10 06:03:45');
INSERT INTO `prices` VALUES ('12', '3', '4', '2017-03-10 06:03:45');
INSERT INTO `prices` VALUES ('13', '5', '1', '2017-03-10 09:03:45');
INSERT INTO `prices` VALUES ('14', '2', '2', '2017-03-10 09:03:45');
INSERT INTO `prices` VALUES ('15', '6', '3', '2017-03-10 09:03:45');

DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) DEFAULT NULL,
  `category_id` int(11) NOT NULL,
  `created_at` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `f_category` (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `products` VALUES ('1', 'apples', '1', '2017-01-10');
INSERT INTO `products` VALUES ('2', 'pears', '1', '2017-01-10');
INSERT INTO `products` VALUES ('3', 'tomatoes', '2', '2017-01-10');
INSERT INTO `products` VALUES ('4', 'walnuts', '3', '2017-01-10');

Answer 1

SELECT products.product_name, categories.category_name, p.id,p.price
FROM products
INNER JOIN categories ON products.category_id = categories.id
INNER JOIN (
SELECT p.id,p.price, p.product_id,
         if(p.product_id <> @p, @rn:=1,@rn:=@rn+1) rn,
         @p:= p.product_id
FROM (select @rn:=0, @p:=0) rn,prices p
ORDER BY p.product_id, p.id DESC 
) AS p on p.product_id = products.id and (p.rn in (1,2))
order by products.id, p.id desc;

注意:- 番茄是一种水果。