在 mysql(5.1.36) 中有很多关于完全连接的问题。当然,解决方案是连接-联合组合。
我的问题是我有两个这样的临时表:
CREATE TEMPORARY TABLE wConfs
(
idWorker INT,
confs SMALLINT
) ENGINE=INNODB;
CREATE TEMPORARY TABLE wRejects
(
idWorker INT,
rejects SMALLINT
) ENGINE=INNODB;
JOIN-UNION mix 不能用于完全连接临时表,因为它会导致ERROR 1137 (HY000): Can't reopen table
。
我的问题是 - 在临时表上实现完全联接的最简单解决方案是什么,具有最佳性能?
编辑:加入-联合混合:
SELECT wc.idWorker, wc.confs, wr.rejects FROM wConfs wc LEFT JOIN wRejects wr
ON (wr.idWorker = wc.idWorker)
UNION
SELECT wc.idWorker, wc.confs, wr.rejects FROM wConfs wc RIGHT JOIN wRejects wr
ON (wr.idWorker = wc.idWorker);
尝试创建第三个临时表,其中包含来自两个表中的不同idWorker
值,然后在 JOIN-UNION 组合中使用它,例如 -
CREATE TEMPORARY TABLE all_id
SELECT idWorker FROM wConfs UNION SELECT idWorker FROM wRejects;
SELECT t.idWorker, c.confs, r.rejects FROM all_id t
LEFT JOIN wConfs c
ON t.idWorker = c.idWorker
LEFT JOIN wRejects r
ON t.idWorker = r.idWorker;
我找到了另一种解决方法。我还创建了第三个临时表:
DROP TABLE IF EXISTS wResults;
CREATE TEMPORARY TABLE wResults
(
idWorker INT PRIMARY KEY,
rejects SMALLINT,
confs SMALLINT
) ENGINE=INNODB;
现在我像这样插入行:
INSERT INTO wResults(idWorker, confs)
SELECT idWorker, confs FROM wConfs;
INSERT INTO wResults(idWorker, rejects)
SELECT wr.idWorker, wr.rejects FROM wRejects wr
ON DUPLICATE KEY UPDATE rejects = wr.rejects;
它就像一个魅力。我想如果有很多数据要插入 - 它会很慢,但我的表格相当小。无论如何,谢谢大家!