python - 涉及两个表的SQL语句

Question

我有两个表，sensor 和 sensor_desc。我想从最后插入 sensor_id 的两个表中检索 sensor.date 和 sensor_desc.location。

我试过这段代码，但行不通。

        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

传感器

+-----------+---------------------+-------+
| sensor_id | date                | value |
+-----------+---------------------+-------+
| 12345     | 2015-08-17 10:16:41 | NULL  |
| 12345     | 2015-08-17 10:17:29 | NULL  |
| 12345     | 2015-08-17 10:18:06 | NULL  |
| 12345     | 2015-08-17 13:28:55 |   1   |
| 12345     | 2015-08-17 13:29:49 |   1   |
+-----------+---------------------+-------+

传感器描述

+-----------+--------------------+-------------+
| 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

我认为结合你的两个查询得到这个:

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 语法，但我想强调您已经离可行的方法有多近了。