MySQL SELECT with 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)] .也就是说，timestamp 介于 1483527932 和 1507058076 之间的 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 一起选择最新的。