MySQL - 合并两个表 - 有条件

Question

我想将两个像这样的表合并到一个表中，并为重复的键行添加合并表中最旧的 DateAdded 值。

(Key1,Key2) 是主键。

+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field     | Type      | Null | Key | Default           | Extra                       |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| Key1      | int(10)   | NO   | PRI |                   |                             |
| Key2      | int(10)   | NO   | PRI |                   |                             |
| DateAdded | DATETIME  | NO   |     |                   |                             |
+-----------+-----------+------+-----+-------------------+-----------------------------+

Answer 1

请随意将表名称更改为您的具体情况:

INSERT INTO table_merged (Key1, Key2, dateAdded)
SELECT Key1, Key2, MIN(dateAdded) dateAdded
FROM (
    SELECT Key1, Key2, dateAdded
    FROM table1
    UNION ALL
    SELECT Key1, Key2, dateAdded
    FROM table2
  ) a
GROUP BY Key1, Key2  

更新:或者，这也应该有效:

INSERT INTO table_merged (Key1, Key2, dateAdded)
SELECT Key1, Key2, dateAdded
FROM table1
ON DUPLICATE KEY UPDATE
  dateAdded = CASE WHEN VALUES(dateAdded) < dateAdded THEN VALUES(dateAdded) ELSE dateAdded END;

INSERT INTO table_merged (Key1, Key2, dateAdded)
SELECT Key1, Key2, dateAdded
FROM table2
ON DUPLICATE KEY UPDATE
  dateAdded = CASE WHEN VALUES(dateAdded) < dateAdded THEN VALUES(dateAdded) ELSE dateAdded END;