MySQL关联表中获取指定房间各设备最新数据的方法

你的问题出在GROUP BY的逻辑上：当按devices.DeviceID分组后，max(messdaten.Timestamp)能正确拿到每个设备的最新时间，但messdaten.Value没有被聚合处理，数据库会随机返回该设备的某一条记录的值，自然和最新时间不匹配。下面是两种可行的解决思路：

方案一：子查询获取每个设备的最新时间后关联 #

先通过子查询得到每个设备的最新Timestamp，再将这个结果与devices和messdaten关联，确保时间和值一一对应：

SELECT 
    d.DeviceID, 
    d.Floor, 
    d.Room, 
    d.Measurement, 
    md.Timestamp, 
    md.Value
FROM devices d
LEFT JOIN (
    -- 子查询：获取每个设备的最新时间
    SELECT DeviceID, MAX(Timestamp) AS LatestTime
    FROM messdaten
    GROUP BY DeviceID
) latest_md ON d.DeviceID = latest_md.DeviceID
LEFT JOIN messdaten md 
    ON d.DeviceID = md.DeviceID 
    AND md.Timestamp = latest_md.LatestTime
WHERE d.Room = 'WC' -- 替换为你需要的房间，或保留原条件Room = (SELECT Room FROM devices WHERE DeviceID = 1)
ORDER BY d.Measurement ASC;

方案二：使用窗口函数（推荐，适合支持SQL:2003的数据库） #

如果你的数据库支持窗口函数（如MySQL 8.0+、PostgreSQL、SQL Server等），可以用ROW_NUMBER()给每个设备的记录按时间倒序编号，取编号为1的那条（即最新记录）：

SELECT 
    DeviceID, 
    Floor, 
    Room, 
    Measurement, 
    Timestamp, 
    Value
FROM (
    SELECT 
        d.DeviceID, 
        d.Floor, 
        d.Room, 
        d.Measurement, 
        md.Timestamp, 
        md.Value,
        -- 按设备分组，时间倒序排，每条记录编号
        ROW_NUMBER() OVER (PARTITION BY d.DeviceID ORDER BY md.Timestamp DESC) AS rn
    FROM devices d
    LEFT JOIN messdaten md ON d.DeviceID = md.DeviceID
    WHERE d.Room = 'WC'
) t
WHERE rn = 1 -- 只保留每个设备的最新记录
ORDER BY Measurement ASC;

这两种方案都能正确获取指定房间内每个设备的最新Timestamp和对应的Value，窗口函数的写法更简洁易读，推荐优先使用。

内容的提问来源于stack exchange，提问作者zYan