Mysql完全外部连接临时表

Question

关于完全加入mysql有很多问题(5.1.36)。当然，解决方案是联合-联盟组合。

我的问题是，我有两个临时表，是这样的：

CREATE TEMPORARY TABLE wConfs
(
    idWorker INT,
    confs SMALLINT
) ENGINE=INNODB;

CREATE TEMPORARY TABLE wRejects
(
    idWorker INT,
    rejects SMALLINT
) ENGINE=INNODB;

连接-联合组合不能用于完全连接临时表，因为它将导致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);

Answer 1

尝试从两个表创建具有不同idWorker值的第三个临时表，然后在连接-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;

Answer 2

我找到了另一种解决办法。我还创建了第三个临时表：

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;

就像一种魅力。我想，如果有很多数据要插入，这将是相当缓慢，但我的桌子相当小。不管怎样，谢谢大家！