mysql - 将单行连接到查询

Question

我需要从另一个表中获取一个值，但可能有 5/6 个结果，我只需要显示最新的一个。我尝试了以下方法:

SELECT s.Mileage
     , s.PurchasePrice
     , v.make
     , v.model
     , v.vrm
     , c.CleanLive 
  FROM StockBook s
  LEFT 
  JOIN Vehicles v
    ON v.VehicleID = s.VehicleID
  LEFT 
  JOIN CapVals c
    ON c.LeadID = (SELECT C1.CleanLive 
                   FROM CapVals C1
                  WHERE s.LeadID = c.LeadID
                  ORDER 
                     BY C1.Date 
                  LIMIT 1
                ) 
 ORDER 
    BY StockBookID

它用作查询但不显示 CleanLive 值。

我在这里设置了一个示例数据集和 DB Fiddle:

CREATE TABLE `Vehicles` (
  `VehicleID` int(11) NOT NULL,
  `vrm` varchar(15) NOT NULL,
  `make` varchar(40) NOT NULL,
  `model` varchar(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `StockBook` (
  `StockBookID` int(11) NOT NULL,
  `VehicleID` int(11) NOT NULL,
  `LeadID` int(11) NOT NULL,
  `Mileage` int(11) NOT NULL,
  `PurchasePrice` decimal(15,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `CapVals` (
  `CapValsID` int(11) NOT NULL,
  `LeadID` int(11) DEFAULT NULL,
  `CleanLive` int(11) DEFAULT NULL,
  `Date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Vehicles` (`VehicleID`, `vrm`, `make`, `model`) VALUES
(1, 'M900WRD', 'Vauxhall', 'Signum');

INSERT INTO `StockBook` (`StockBookID`, `LeadID`, `VehicleID`, `Mileage`, `PurchasePrice`) VALUES
(1, 1, 1, 17000, 15000.00);

INSERT INTO `CapVals` (`CapValsID`, `LeadID`, `CleanLive`, `Date`) VALUES
(6455, 1, 1540, '2019-12-04 15:02:29'),
(6456, 1, 1540, '2019-12-04 15:02:29'),
(6457, 1, 1540, '2019-12-04 15:02:29');

https://www.db-fiddle.com/f/b4fQuMVpXHGxqgYJ4ia92w/4

Answer 1

你可以试试这个

SELECT Stock.Mileage, Stock.PurchasePrice, Vehi.make, Vehi.model, Vehi.vrm,
(SELECT CleanLive from CapVals a WHERE a.LeadID = Stock.LeadID ORDER BY  DATE DESC LIMIT 1) AS CleanLive
FROM StockBook Stock
LEFT JOIN Vehicles Vehi
ON Stock.VehicleID=Vehi.VehicleID
ORDER BY StockBookID