mysql连接3个没有关系的表并从中获取最后的数据

vecaoik1  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(430)

我在mysql数据库中有三个表,我只想从这三个表中得到最后输入的数据(最后输入的数据来自表1,最后输入的数据来自表2,最后输入的数据来自表3)。我可以通过以下方式获得所有表的第一个输入数据:

SELECT dnevna.temp AS temp1, dnevna.hum AS hum1, podrum.temp AS temp2, podrum.hum AS hum2, spremnik_status.spremnik AS status_spremnik
FROM dnevna, podrum,spremnik_status
LIMIT 1

所以我试着用这种方式来输入最后的数据:

SELECT dnevna.temp AS temp1, dnevna.hum AS hum1, podrum.temp AS temp2, podrum.hum AS hum2, spremnik_status.spremnik AS status_spremnik
FROM dnevna, podrum, spremnik_stauts
ORDER BY dnevna.id, podrum.id,spremnik_stauts.id DESC
LIMIT 1

但我犯了个错误,这不是一个好方法…怎么做?。
表之间没有关系,因为表是由两个mcu-s随机更新的,它们在不同的时间上载数据,因此时间、id自动增量等不匹配。另外,我在3个表中的2个表中有相同的列名(temp,hum是2个表中的相同列名..所以这有问题吗?我需要重命名吗?)
我需要sql选择从3个表中生成一个php json字符串,这样我以后就可以为android studio、mcu-u阅读等进行编码。

cmssoen2

cmssoen21#

您可以从每个表中检索最后一条记录,并交叉联接这三个查询:

SELECT     *
FROM       (SELECT   temp AS temp1, hum AS hum1
            FROM     dnevna
            ORDER BY id DESC
            LIMIT    1) a
CROSS JOIN (SELECT   temp AS temp2, hum AS hum2
            FROM     podrum
            ORDER BY id DESC
            LIMIT    1) b
CROSS JOIN (SELECT   spremnik AS status_spremnik
            FROM     spremnik_status
            ORDER BY id DESC
            LIMIT    1) c
v1uwarro

v1uwarro2#

SELECT  tb1.temp as temp1, tb1.hum  as hum1, 
    tb2.temp AS temp2, tb2.hum AS hum2,   
    tb3.spremnik AS status_spremnik
FROM 
(SELECT * FROM dnevna ORDER BY id DESC LIMIT 1) AS tb1,
(SELECT * FROM podrum ORDER BY id DESC LIMIT 1) AS tb2,
(SELECT * FROM spremnik_status ORDER BY id DESC LIMIT 1) AS tb3

相关问题