mysql - 如何在SQL中合并两个表？

我有两张桌子：

Table 1 = idhash - username - usermail
Table 2 = idhash - views    - fistseen - lastseen

现在我要将这些表合并到新表：

Table 3 = idhash - username - usermail - firstseen - lastseen

*请注意，我要删除“视图”列。
我试过在google上找到的解决方案，但到目前为止，它们似乎都不起作用。
并非表2中的所有idhash列在表1中都有相应的idhash。Stiil存储用户名和用户邮件为空的“不匹配”行

最佳答案

此查询将提供结果：

SELECT A.*, B.firstseen, B.lastseen
FROM [Table 1] A
LEFT JOIN [Table 2] B
ON A.idhash = B.idhash

并在[表3]中插入和更新结果：

INSERT INTO [Table 3](idhash, username, usermail, firstseen, lastseen)
SELECT A.*, B.firstseen, B.lastseen
FROM [Table 1] A
LEFT JOIN [Table 2] B
ON A.idhash = B.idhash
LEFT JOIN [Table 3] C
ON A.idhash = C.idhash
WHERE C.idhash IS NULL

-- For SQL Server
UPDATE T3
SET firstseen = T1.firstseen,
    lastseen = T1.lastseen
FROM [Table 3] T3
INNER JOIN (SELECT A.*, B.firstseen, B.lastseen
            FROM [Table 1] A
            LEFT JOIN [Table 2] B
            ON A.idhash = B.idhash) T1
WHERE T3.firstseen != T1.firstseen OR T3.lastseen != T1.lastseen