I have 2 mysql tables with the same rows, on both the tables some information is missing and now I need to merge the two tables into one table with the complete information.
我有两个具有相同行的mysql表,在这两个表上缺少一些信息,现在我需要将两个表合并到一个包含完整信息的表中。
This is how the tables look like:
这就是表格的样子:
Table1:
Name | Adres | Postal
------------------------------
Koen | Stationsweg |
| Marktplein | 4342FG
Table 2:
Name | Adres | Postal
------------------------------
| Stationsweg | 4368RT
Bert | Marktplein |
The final table needs to look like:
决赛桌需要看起来像:
Name | Adres | Postal
------------------------------
Koen | Stationsweg | 4368RT
Bert | Marktplein | 4342FG
1 个解决方案
#1
2
SELECT MAX(COALESCE(a.Name, b.name)) name,
a.adres,
MAX(COALESCE(a.postal, b.postal)) postal
FROM table1 a
LEFT JOIN table2 b
ON a.adres = b.adres
GROUP BY a.adres
if you want the result of the above query to be inserted let's say on another table: table3, Use INSERT INTO...SELECT
如果你想要插入上面查询的结果,让我们说另一个表:table3,使用INSERT INTO ... SELECT
INSERT INTO table3 (name, adres, postal)
SELECT MAX(COALESCE(a.Name, b.name)) name,
a.adres,
MAX(COALESCE(a.postal, b.postal)) postal
FROM table1 a
LEFT JOIN table2 b
ON a.adres = b.adres
GROUP BY a.adres
#1
2
SELECT MAX(COALESCE(a.Name, b.name)) name,
a.adres,
MAX(COALESCE(a.postal, b.postal)) postal
FROM table1 a
LEFT JOIN table2 b
ON a.adres = b.adres
GROUP BY a.adres
if you want the result of the above query to be inserted let's say on another table: table3, Use INSERT INTO...SELECT
如果你想要插入上面查询的结果,让我们说另一个表:table3,使用INSERT INTO ... SELECT
INSERT INTO table3 (name, adres, postal)
SELECT MAX(COALESCE(a.Name, b.name)) name,
a.adres,
MAX(COALESCE(a.postal, b.postal)) postal
FROM table1 a
LEFT JOIN table2 b
ON a.adres = b.adres
GROUP BY a.adres