使用 INNER JOIN 从表中检索最新的时间戳行 [英] Retrieve latest timestamp row from table using INNER JOIN

问题描述

想要从表中检索名称行的最新时间戳.然而，只有表位置"有时间戳.我使用 INNER JOIN 将表elderly1"和location"连接在一起.我只能表明我已经从位置"表中检索到了最新的时间戳，但没有检索到最新的纬度"和经度".请帮忙.

want to retrieve the latest timestamp of the row of name from table. However only the table "location" has the timestamp. I used INNER JOIN to join the tables "elderly1" and "location" together. i am only able to show that i have retrieved the latest timestamp but not the latest "latitude" and "longitude" from the table "location". Please help.

SELECT e.name,e.illness, e.patient_id,e.patient_image,e.area, e.arduino_mac,
       l.arduino_mac, l.latitude, l.longitude,MAX(l.timestamp) as ts 
FROM elderly1 e 
JOIN location l 
    on e.arduino_mac = l.arduino_mac 
WHERE e.arduino_mac = l.arduino_mac 
GROUP BY e.name 
ORDER BY l.timestamp

推荐答案

不知道每个表的候选键就很难多说，但总的来说你必须确保 SELECT 子句是在功能上依赖于 GROUP BY 子句.鉴于您对问题的表述，我建议如下:

It's difficult to say much without knowing the candidate keys of each table, but in general you must make sure that the SELECT clause is functionally dependent of the GROUP BY clause. Given you formulation of the problem I would suggest something like:

SELECT e.name,e.illness, e.patient_id,e.patient_image,e.area, e.arduino_mac,
       l.arduino_mac, l.latitude, l.longitude, l.timestamp as ts 
FROM elderly1 e 
JOIN ( SELECT l1.arduino_mac, l1.latitude, l1.longitude, l1.timestamp
       FROM location l1
       WHERE timestamp = ( SELECT MAX(timestamp)
                           FROM LOCATION l2
                           WHERE l1.arduino_mac = l2.arduino_mac )
) as l
    on e.arduino_mac = l.arduino_mac 
ORDER BY l.timestamp

这篇关于使用 INNER JOIN 从表中检索最新的时间戳行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！