mysql - 选择最后一个不同的记录，其中 table1.column1 = table2.column2

Question

我有来自同一数据库的两个表:

表格读数:

表传感器:

我正在尝试创建一个查询，对于在 SENSORS 中注册的每个传感器，该查询将返回在 READINGS 中注册的最后一行。请注意，同一传感器在 READINGS 中可以有多个寄存器，但在 SENSORS 中只能有一个。

结果应该是这样的:

数据库转储:https://dl.dropboxusercontent.com/u/85576999/redeSensores.sql

Answer 1

http://sqlfiddle.com/#!9/95818/5

SELECT readings.*
FROM readings
INNER JOIN sensors
ON sensors.idSensor = readings.sensorid
LEFT JOIN readings r
ON r.sensorid = readings.sensorid
  AND r.`datetime` > readings.`datetime`
WHERE r.id IS NULL

或者，如果您需要两个表中的信息:

http://sqlfiddle.com/#!9/95818/6

SELECT readings.*, 
       sensors.*
FROM readings
INNER JOIN sensors
ON sensors.idSensor = readings.sensorid
LEFT JOIN readings r
ON r.sensorid = readings.sensorid
  AND r.`datetime` > readings.`datetime`
WHERE r.id IS NULL

更新最后一天的平均值

http://sqlfiddle.com/#!9/95818/10

SELECT t.*, 
   AVG(r_avg.temperature),
   AVG(r_avg.pollution),
   AVG(r_avg.noise),
   AVG(r_avg.humidity)
FROM (SELECT readings.*, sensors.*
FROM readings
INNER JOIN sensors
ON sensors.idSensor = readings.sensorid
LEFT JOIN readings r
ON r.sensorid = readings.sensorid
  AND r.`datetime` > readings.`datetime`
WHERE r.id IS NULL 
) t
LEFT JOIN readings r_avg
ON t.sensorid = r_avg.sensorid
  AND r_avg.`datetime` >= DATE_ADD(t.datetime,INTERVAL -1 DAY)
GROUP BY t.id;

UPDATE 2要过滤聚合记录，您可以使用HAVING:

http://sqlfiddle.com/#!9/95818/11

HAVING AVG(r_avg.temperature)!=42