由于您手边有大量数据,我建议您先合并日期和时间列.然后,您可以有效地使用索引.如果你不这样做,你将不得不做类似的事情
...WHERE CONCAT(date, ' ', time) = SELECT MAX(CONCAT(date, ' ', time)) ...
所以,首先对两个表都这样做.
ALTER TABLE tableA ADD COLUMN creation_date datetime; /*or whatever name, just make it meaningful and don't use keywords*/
UPDATE tableA SET creation_date = CONCAT(date, ' ', time);
ALTER TABLE tableA DROP COLUMN date, DROP COLUMN time;
CREATE INDEX idx_dt_tableA_creation ON tableA(creation_date);
然后,您可以将两个表插入combine_table
(注意,保留完整性,第二个选项要好得多).
INSERT INTO combined_table
SELECT col1, col2, creation_date
FROM (
SELECT col1, col2, creation_date
FROM tableA
UNION ALL
SELECT col1, col2, creation_date
FROM tableB
) sq /*subquery_alias*/
WHERE creation_date = (SELECT MAX(creation_date) FROM (
SELECT col1, col2, creation_date
FROM tableA
UNION ALL
SELECT col1, col2, creation_date
FROM tableB
) another_sq
WHERE sq.col1 = another_sq.col1
)
;
尽管如此,如果您真的拥有那么多数据,这将是一项繁重的操作.
现在我想到了,有一个更好的方法.
首先插入表A.
INSERT INTO combined_table
SELECT * FROM tableA;
INSERT INTO combined_table c
SELECT * FROM tableB b
ON DUPLICATE KEY UPDATE
/*you can skip col1, since it's the identifying primary key here*/
col2 = IF(b.creation_date > c.creation_date, b.col2, c.col2),
creation_date = IF(b.creation_date > c.creation_date, b.creation_date, c.creation_date)
;