包含两个表的SQL语句

Question

我有两张表，传感器和传感器描述。我想检索传感器日期和传感器_描述位置从最后插入传感器_id的两个表中。在

我试过这个密码，但没用。在

        cur = con.cursor()
        cur.execute("Select sensor_id from sensor order by date desc limit 1")
        con.commit()
        last= cur.fetchall()
        print '%s' %last
        cur1= con.cursor()
        cur.execute("Select sensor.date, sensor_desc.location from sensor, sensor_desc where sensor.sensor_id= %s AND sensor_desc.sensor_id=%s",(last, last))
        con.commit()
        lastseen = cur1.fetchall()
         print '%s'%lastseen

传感器

^{pr2}$

传感器描述

+-----------+--------------------+-------------+
| sensor_id | description        | location    |
+-----------+--------------------+-------------+
| 12341     | Motion Sensor      | Kitchen     |
| 12342     | Motion Sensor      | Toilet      |
| 12343     | Motion Sensor      | Living Room |
| 12344     | Motion Sensor      | BedRoom     |
| 12345     | Panic Button       | NULL        |
| 12346     | Temperature Sensor | NULL        |
| 12347     | CO2 Sensor         | NULL        |
+-----------+--------------------+-------------+

Here is the fiddle

Answer 1

您应该运行的SQL语句应该在两个表之间联接，按日期排序，然后只获取第一条记录。在

我在这里使用INNER JOIN，只是因为这样可以省略WHERE子句（我觉得它更可读）。在

SELECT sensor.date, sensor_desc.location 
FROM sensor 
    INNER JOIN sensor_desc ON sensor.sensor_id = sensor_desc.sensor_id
ORDER BY sensor.date DESC
LIMIT 1

Answer 2

我认为把你的两个问题结合起来可以得到：

select sensor.date, sensor_desc.location
from sensor, sensor_desc
where sensor.sensor_id = sensor_desc.sensor_id
order by sensor.date desc
limit 1

这里的很多人都希望您使用inner join语法，但我想强调一下，您已经非常接近某个有效的方法了。在