Mysql JOIN(多个)表 [英] Mysql JOIN (multiple) tables

问题描述

我有3张桌子.它们中的2个是相同的(相同的列，不同的数据)，而第三个具有有关其他2的一些信息数据.数据库如下所示:

I have 3 tables. 2 of them are the same (same columns, different data), and the third has some info data about other 2. Database looks like this:

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

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

I'm trying to JOIN all into one table, which should look like this:

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

任何想法sql查询应该是什么样子?

Any idea how sql query should look like?

谢谢

推荐答案

尝试并集:

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

您可以从这些结果中进行其他选择，然后可以限制，分组或订购:

You can make another select from those results, then you can limit, group or order:

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和表3)中仅保留一行，可以对每个查询使用限制/分组/排序依据(假设您具有列日期):

Edit 2: To have only one row from each table (table 2 and table 3) you can use limit/group by/order by for each query (assuming you have column date):

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

这篇关于Mysql JOIN(多个)表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！