我知道这个问题已经被问过很多次了，但我在执行它时遇到了困难。
我做了一个简化的例子，所以它很容易重现。
我想连接 3 个表，但在最后一个表上我想限制为 2 行 DESC

CREATE TABLE `cars` (
`car_id` int(11) NOT NULL AUTO_INCREMENT,
`plate` varchar(10) NOT NULL,
`km` int(11) NOT NULL,
`status` tinyint(1) NOT NULL,
PRIMARY KEY (`car_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
INSERT INTO `cars` (`car_id`, `plate`, `km`, `status`) VALUES
(1, 'ABC1234', 130123, 1),
(2, 'DEF1234', 100123, 1),
(3, 'QWE1234', 5000, 1),
(4, 'ASD1234', 3000, 1),
(5, 'ZXC1234', 23000, 0);

CREATE TABLE `cars_to_users` (
`car_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
UNIQUE KEY `car_id` (`car_id`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `cars_to_users` (`car_id`, `user_id`) VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 2),
(5, 2);

CREATE TABLE `service` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`car_plate` varchar(10) NOT NULL,
`s_timestamp` int(10) NOT NULL,
`price` double NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=111 ;
INSERT INTO `service` (`id`, `car_plate`, `s_timestamp`, `price`) VALUES
(1, 'ABC1234', 1312300100, 30),
(2, 'DEF1234', 1312300100, 15),
(3, 'QWE1234', 1312300100, 16),
(4, 'ASD1234', 1312300100, 50),
(5, 'ABC1234', 1312300200, 50),
(6, 'DEF1234', 1312300200, 25),
(7, 'QWE1234', 1312300200, 30),
(8, 'ABC1234', 1312300300, 20),
(9, 'ASD1234', 1312300300, 60),
(10, 'ABC1234', 1312300400, 15),
(11, 'ASD1234', 1312300400, 20);

我要的是这个

car_id  plate  km  car_plate  s_timestamp  price  
3 QWE1234 5000 QWE1234 1312300200 30 
3 QWE1234 5000 QWE1234 1312300100 16 
4 ASD1234 3000 ASD1234 1312300400 20 
4 ASD1234 3000 ASD1234 1312300300 60 

“服务”表中 user_id=2 的每辆车的 2 行按 s_timestamp DESC 排序

ORDER BY s_timestamp LIMIT 2 DESC

我尝试了这个查询，但给了我来自“服务”的所有行

SELECT ctu.user_id, c.car_id, c.plate, c.km, s.car_plate, s.s_timestamp, s.price
FROM cars_to_users ctu 
LEFT JOIN cars c ON  ctu.car_id = c.car_id
LEFT JOIN service s ON c.plate = s.car_plate 
WHERE ctu.user_id = '2' 
AND c.status = 1

如果我添加“GROUP BY c.car_id”，我每辆车只能得到 1 行，而不是我想要的 2 行

我尝试了很多查询，但没有得到我想要的。

要记住的一点是，“service”表有超过 900 万行和比示例更多的数据，并且还在增长。

最佳答案

这个答案相当复杂。而且我不确定它在您的数据库上的表现如何。

SELECT ctu.user_id, c.car_id, c.plate, c.km, s.car_plate, s.s_timestamp, s.price
FROM cars_to_users ctu 
LEFT JOIN cars c ON  ctu.car_id = c.car_id
LEFT JOIN service s ON c.plate = s.car_plate
JOIN 
(
  SELECT service.car_plate,max(service.s_timestamp) as s_timestamp
  FROM service
  JOIN 
  (
    SELECT car_plate, max(s_timestamp) as s_timestamp FROM service GROUP BY car_plate
  ) as max_timestamp ON max_timestamp.car_plate = service.car_plate AND service.s_timestamp < max_timestamp.s_timestamp
  GROUP BY service.car_plate
) as max_2_timestamp ON s.car_plate = max_2_timestamp.car_plate AND s.s_timestamp >= max_2_timestamp.s_timestamp
WHERE ctu.user_id = '2' 
AND c.status = 1
ORDER BY s_timestamp DESC

我猜你可以像这样先将 2 个子查询放在临时表中

DROP TABLE IF EXISTS max_timestamp;
DROP TABLE IF EXISTS max_2_timestamp;

CREATE TEMPORARY table max_timestamp SELECT car_plate, max(s_timestamp) as s_timestamp FROM service GROUP BY car_plate;

CREATE TEMPORARY table max_2_timestamp
(
  SELECT service.car_plate,max(service.s_timestamp) as s_timestamp
  FROM service
  JOIN max_timestamp ON max_timestamp.car_plate = service.car_plate AND service.s_timestamp < max_timestamp.s_timestamp
  GROUP BY service.car_plate
);

SELECT ctu.user_id, c.car_id, c.plate, c.km, s.car_plate, s.s_timestamp, s.price
FROM cars_to_users ctu 
LEFT JOIN cars c ON  ctu.car_id = c.car_id
LEFT JOIN service s ON c.plate = s.car_plate
JOIN max_2_timestamp ON s.car_plate = max_2_timestamp.car_plate AND s.s_timestamp >= max_2_timestamp.s_timestamp
WHERE ctu.user_id = '2' 
AND c.status = 1
ORDER BY s_timestamp DESC;

编辑:另一种选择

您将只有一个查询，但我无法检查它在您的系统中是否足够高效。

让我们创建一个函数，为 Service

CREATE FUNCTION LatestService (car_plate varchar(10))
RETURNS int(10)
RETURN 
(SELECT s_timestamp
FROM service s
WHERE s.car_plate=`car_plate`
ORDER BY s.s_timestamp desc
LIMIT 1,1);

然后您可以使用该函数执行查询。

SELECT ctu.user_id, c.car_id, c.plate, c.km, s.car_plate, s.s_timestamp, s.price
FROM cars_to_users ctu 
LEFT JOIN cars c ON  ctu.car_id = c.car_id
LEFT JOIN service s ON c.plate = s.car_plate 
WHERE ctu.user_id = '2' 
AND c.status = 1
AND s.s_timestamp >= LatestService(s.car_plate);

关于mysql - JOIN 3 tables and (LIMIT 2 rows ORDER BY time DESC)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/6956445/