我有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 |
+-------------------------------+
我正在尝试将所有表合并到一个表中,该表应如下所示:
+-------------------------------+
| Name | Temperature | Pressure |
+-------------------------------+
| Table 2 | 22 | 1013 |
| Table 3 | 20 | 1009 |
+-------------------------------+
知道sql查询的样子吗?
谢谢
解决方法:
尝试工会:
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
标签:mysql
来源: https://codeday.me/bug/20191012/1899589.html