mysql - 选择指定日期之前的最后一个事件

Question

假设我有下面描述的 2 个表: EventData几乎每 60 秒包含每个设备的 gps 事件(意味着大量数据)

CREATE TABLE IF NOT EXISTS `EventData` (
  `accountID` varchar(32) NOT NULL,
  `deviceID` varchar(32) NOT NULL,
  `timestamp` int(10) unsigned NOT NULL,
  `statusCode` int(10) unsigned NOT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `gpsAge` int(10) unsigned DEFAULT NULL,
  `speedKPH` double DEFAULT NULL);

Device包含设备信息，

CREATE TABLE IF NOT EXISTS `Device` (
  `deviceID` varchar(32) NOT NULL,
  `groupID` varchar(32) DEFAULT NULL,
  `equipmentType` varchar(40) DEFAULT NULL,
  `speedLimitKPH` double DEFAULT NULL,
  `deviceType` varchar(24) DEFAULT NULL,
  `pushpinID` varchar(32) DEFAULT NULL,
  `description` varchar(50) DEFAULT NULL);

我想要一个返回每个设备在给定时间戳 ( timestamp<$givenTimestamp ) 之前的最后一个事件的查询，并且它必须是单个查询，否则会花费很多时间。有什么办法吗？

---

我试过这个:

SELECT
    d.deviceID,
    d.description,
    d.pushpinID,
    e.latitude,
    e.longitude,
    MAX(e.timestamp) as timestamp
FROM
    Device as d
INNER JOIN
    EventData as e
        ON d.deviceID = e.deviceID
WHERE
    timestamp <= $time
GROUP BY
    (e.deviceID);

但它没有返回我想要的。它返回给定时间之前的最后一个事件，而不是最后一个事件。

Answer 1

试试这个查询:

select d.deviceID ,d.description,d.pushpinID,e.latitude,e.longitude, 
       (select max(timestamp) from EventData where deviceID=e.deviceID) as timestamp
FROM Device as d INNER JOIN EventData as e ON d.deviceID=e.deviceID WHERE 
  timestamp < '$time' GROUP BY e.deviceID