Mysql JOIN(多个)表

Question

我有 3 个表。其中 2 个是相同的(相同的列，不同的数据)，第三个有一些关于其他 2 个的信息数据。数据库如下所示:

表 1:

+--------------+
|  ID | Name   |
+--------------+
| 1  | Table 2 |
| 2  | Table 3 |
+--------------+

表 2:

+-------------------------------+
| Name | Temperature | Pressure |
+-------------------------------+
| Table 2 |    22    | 1013     |
+-------------------------------+

表 3:

+-------------------------------+
| Name | Temperature | Pressure |
+-------------------------------+
| Table 3 |    20    | 1009     |
+-------------------------------+

我正在尝试将所有 JOIN 合并到一张表中，它应该如下所示:

+-------------------------------+
| Name | Temperature | Pressure |
+-------------------------------+
| Table 2 |    22    | 1013     |
| Table 3 |    20    | 1009     |
+-------------------------------+

知道 sql 查询应该是什么样子吗？

谢谢

Answer 1

尝试合并:

SELECT table1.name, temperature, pressure 
FROM table1 inner join table2 ON
table1.name = table2.name
UNION
SELECT table1.name, temperature, pressure 
FROM table1 inner join table3 ON
table1.name = table3.name

编辑:您可以从这些结果中再次选择，然后您可以限制、分组或排序:

SELECT * FROM
(
    SELECT table1.name, temperature, pressure 
    FROM table1 inner join table2 ON
    table1.name = table2.name
    UNION
    SELECT table1.name, temperature, pressure 
    FROM table1 inner join table3 ON
    table1.name = table3.name
) as JoinedTable
LIMIT 0, 1

编辑 2:要让每个表(表 2 和表 3)只有一行，您可以对每个查询使用限制/分组依据/排序依据(假设您有日期列):

SELECT table1.name, temperature, pressure 
FROM table1 inner join table2 ON
table1.name = table2.name
ORDER BY date DESC
LIMIT 0, 1
UNION
SELECT table1.name, temperature, pressure 
FROM table1 inner join table3 ON
table1.name = table3.name
ORDER BY date DESC
LIMIT 0, 1