基于时间戳的MySQL选择与IF

Question

我有一个MySQL数据库，其中有3个表，如下所示。

Readings: (_id, value, timestamp, sensorId)
Sensor: (_id)
DeviceSensor: (deviceId, sensorId, timestamp)

每个Reading都有收集数据的时间戳。Sensor是Reading的所有者。DeviceSensor包含Device和Sensor之间的关系，包括关系开始时的时间戳。

在我的体系结构中，当Device与Sensor配对时，Sensor忽略了以前的Devices。

我需要的是查询所有的Readings，并在每次阅读时将它们与相应的Device关联起来。例如：

DeviceSensor = [(1,1,1483527932),(1,2,1507058076)]
Sensor = [(1)]
Readings = [(1,5,1483527932,1),(2,3,1493527932,1),(3,7,1507058076,1)]

使用这些数据，我的查询将返回：[(1,5,1483527932,1),(2,3,1493527932,1),(3,7,1507058076,2)]。也就是说，在Readings和1507058076之间具有timestamp的Readings应该返回sensorId = 1，1507058076之后的Readings应该返回sensorId = 2。

Answer 1

SELECT r._id
      ,r.value
      ,r.timestamp
      ,(SELECT max(ds.deviceId) as deviceId
          FROM DeviceSensor as ds
         WHERE ds.timestamp <= r.timestamp
           AND ds.sensorId = r.sensorId
         ORDER BY ds.timestamp DESC
       LIMIT 1) as deviceId
  FROM Readings as r

子选择从DeviceSensor中选择用于主选择的当前传感器的记录，仅限于那些与读取时间戳成对的记录。LIMIT和ORDER BY ds.timestamp DESC一起选择其中的最新版本。