使用 MySQL 将一个表连接到另一个表中的最新行

Question

我想以特殊方式连接两个表，第一个表是设备，其中包含设备列表。

第二个表是datalog，每次轮询devices 中的设备时都会存储一些数据。

设备表：

+----------+------------+----------------------------+---------------------+
| deviceId | deviceName | deviceDescription          | timeCreated         |
+----------+------------+----------------------------+---------------------+
|        1 | System 1   | Main System in Server Room | 2010-01-01 00:00:00 |
|        2 | System 2   | Outdoor System             | 2010-01-01 00:00:00 |
+----------+------------+----------------------------+---------------------+

数据日志表：

+----+---------------------+----------+-----------+---------+
| id | time_stamp          | DeviceId | FuelLevel | Voltage |
+----+---------------------+----------+-----------+---------+
|  1 | 2010-01-01 00:00:00 |        1 |        60 |     220 |
|  2 | 2010-01-01 00:00:00 |        2 |        20 |     221 |
|  3 | 2010-01-02 00:00:00 |        1 |       100 |     219 |
|  4 | 2010-01-02 00:00:00 |        2 |       100 |     222 |
|  5 | 2010-01-03 00:00:00 |        1 |        80 |     219 |
|  6 | 2010-01-03 00:00:00 |        2 |        99 |     220 |
+----+---------------------+----------+-----------+---------+

目前，我正在使用数据日志表上的查询获取每个设备的最新数据：

 Where DeviceId = 1 ORDER BY timestamp DESC LIMIT 1

我想要什么是一个返回所有设备列表的查询，其中的列与每个设备的最新数据连接在一起，如下所示：

+----------+------------+----------------------------+---------------------+-----------+---------+
| deviceId | deviceName | deviceDescription          | time_stamp          |FuelLevel  | Voltage |
+----------+------------+----------------------------+---------------------+-----------+---------+
|        1 | System 1   | Main System in Server Room | 2010-01-03 00:00:00 |        80 |     219 |
|        2 | System 2   | Outdoor System             | 2010-01-03 00:00:00 |       99  |     220 |
+----------+------------+----------------------------+---------------------+-----------+---------+

Answer 1

您无法在外部级别执行“限制 1”，您会丢失您正在寻找的内容...所有设备最后一个条目。对每个设备的最后一个 ID 使用预查询，然后连接回来...

select 
        Devices.*,
        DataLog.Time_Stamp,
        DataLog.FuelLevel,
        DataLog.Voltage
    from
        ( select DeviceID,
                 max( ID ) LastActionID
              from
                 DataLog
              group by 
                 1 ) LastInstance
        join DataLog
            on LastInstance.LastActionID = DataLog.ID
        join Devices 
            on LastInstance.DeviceID = Devices.DeviceID
   order by 
       Devices.DeviceName

根据您的最后一条评论，我实际上会更改为类似...

使用“LastLogID”更新您的设备表。然后，通过触发器插入到您的数据日志表中，立即使用该新 ID 更新设备表...这样，您就不需要直接预先查询数据日志...您已经拥有最后一个 ID 并运行从该 ID 直接连接到由该 ID 连接的数据日志。

Answer 2

我知道这很糟糕，不优雅且耗时，但这个查询有效：

SELECT deviceId,deviceName,deviceDescription,
  (SELECT time_stamp FROM datalog
    WHERE datalog.DeviceId=devices.deviceId
    ORDER BY time_stamp DESC LIMIT 0,1) time_stamp,
  (SELECT FuelLevel FROM datalog
    WHERE datalog.DeviceId=devices.deviceId
    ORDER BY time_stamp DESC LIMIT 0,1) FuelLevel,
  (SELECT Voltage FROM datalog
    WHERE datalog.DeviceId=devices.deviceId
    ORDER BY time_stamp DESC LIMIT 0,1) Voltage
FROM devices

我尝试使用单个子查询检索多列，但 MySql 抱怨，因为它只需要一列。

Answer 3

顺便说

一句，如果您只想要最新行，那么您可以通过自动增量字段（datalog_table.id）搜索它

SELECT dvc.deviceId,dvc.deviceName,dvc.deviceDescription,
       dtl.time_stamp,dtl.FuelLevel,dtl.Voltage 
FROM  device_table dvc
INNER JOIN datalog_table dtl
ON dtl.DeviceId=dvc.deviceId
ORDER BY dtl.id  LIMIT 1

Answer 4

  SELECT
  d.deviceId, d.deviceName, d.deviceDescription, 
  dl.time_stamp, dl.FuelLevel, dl.Voltage 
  FROM Device d, DataLog dl 
  WHERE d.deviceId=dl.deviceID 
  ORDER BY time_stamp DESC
  LIMIT 1