我有两个名为raspi_E和raspi_F表，结构相同，列id ， address ， device_time 。
raspi_E表中有多个具有相同地址的行，但我想要最旧的行（相对于时间而言）。
例如，我有多次使用device_time值的地址23 2014-08-02 16:00:00 ： 2014-08-02 16:00:02 ： 2014-08-02 16:00:04 ，我想要行包含最旧的device_time ，即2014-08-02 16:00:00
类似地，在raspi_F表中，我有多个具有相同地址的行，我必须得到最旧的，在上面的示例中将是2014-08-02 16:10:00 。
现在我需要在时间2014-08-02 16:00:00加入raspi_E地址23 ，并且我在raspi_F的同一地址23拥有最旧的设备图章2014-08-02 16:10:00 。
我正在尝试使用以下查询：
SELECT raspi_E.address, MIN(raspi_E.device_time) AS time_D , MIN(raspi_F.device_time) AS time_E WHERE (raspi_E.device_time BETWEEN '2014-07-31 16:00:00' AND '2014-07-31 16:10:00' GROUP BY raspi_E.address) AND (raspi_F.device_time BETWEEN '2014-07-31 16:03:00' AND '2014-07-31 16:25:00' GROUP BY raspi_F.address) AND raspi_E.address=raspi_F.address我使用上面的查询得到以下错误
1064 - 您的SQL语法有错误; 查看与您的MySQL服务器版本对应的手册，以便在'WHERE附近使用正确的语法（raspi_E.device_time BETWEEN'2014-07-31 16:00:00'AND'2014-07-31 16：10：0'at at第1行
我使用下面的查询来获取raspi_E中最旧的行值，它工作正常。
SELECT address, MIN(device_time) AS device_time FROM raspi_E WHERE device_time BETWEEN '2014-07-31 16:00:00' AND '2014-07-31 16:10:00' GROUP BY address ORDER BY `device_time` ASCI have two tables with names raspi_E and raspi_F with same structure and the columns id, address, device_time.
There are multiple rows with same address in raspi_E table but I want the oldest row (with respect to time).
For instance, I have the address 23 multiple times with device_time values 2014-08-02 16:00:00, 2014-08-02 16:00:02, 2014-08-02 16:00:04, I want the row containing the oldest device_time, that is 2014-08-02 16:00:00
Similarly, in the raspi_F table, I have multiple rows with same address and I have to get the oldest, in the above example that would be 2014-08-02 16:10:00.
Now I need to join address 23 at raspi_E with time 2014-08-02 16:00:00 and I at raspi_F the same address 23 having oldest device stamp 2014-08-02 16:10:00.
I'm trying to use the following query:
SELECT raspi_E.address, MIN(raspi_E.device_time) AS time_D , MIN(raspi_F.device_time) AS time_E WHERE (raspi_E.device_time BETWEEN '2014-07-31 16:00:00' AND '2014-07-31 16:10:00' GROUP BY raspi_E.address) AND (raspi_F.device_time BETWEEN '2014-07-31 16:03:00' AND '2014-07-31 16:25:00' GROUP BY raspi_F.address) AND raspi_E.address=raspi_F.addressI am getting the following error for using the above query
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (raspi_E.device_time BETWEEN '2014-07-31 16:00:00' AND '2014-07-31 16:10:0' at line 1
I have used the below query to get oldest row value at raspi_E and it worked fine.
SELECT address, MIN(device_time) AS device_time FROM raspi_E WHERE device_time BETWEEN '2014-07-31 16:00:00' AND '2014-07-31 16:10:00' GROUP BY address ORDER BY `device_time` ASC最满意答案
因为你在哪里/分组有问题...这应该为你做的伎俩......
SELECT OldestE.Address, OldestE.ETime, OldestF.FTime from ( SELECT E.address, MIN(E.device_time) ETime from raspi_E E where E.Device_Time between '2014-07-31 16:00:00' AND '2014-07-31 16:10:00' group by E.address ) OldestE JOIN ( SELECT F.address, MIN(F.device_time) FTime from raspi_F F where F.device_time BETWEEN '2014-07-31 16:03:00' AND '2014-07-31 16:25:00' group by F.address ) OldestF ON OldestE.Address = OldestF.AddressSince you were having problems with where / group by... this should do the trick for you...
SELECT OldestE.Address, OldestE.ETime, OldestF.FTime from ( SELECT E.address, MIN(E.device_time) ETime from raspi_E E where E.Device_Time between '2014-07-31 16:00:00' AND '2014-07-31 16:10:00' group by E.address ) OldestE JOIN ( SELECT F.address, MIN(F.device_time) FTime from raspi_F F where F.device_time BETWEEN '2014-07-31 16:03:00' AND '2014-07-31 16:25:00' group by F.address ) OldestF ON OldestE.Address = OldestF.Address更多推荐
发布评论