MySQL：完全外部联接 - 如何合并一列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4415109/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: FULL OUTER JOIN - How do I merge one column?
提问by Mig Cervantez
I have a question regarding a FULL OUTER JOIN in MySQL. I have two (or more tables):
我有一个关于 MySQL 中的 FULL OUTER JOIN 的问题。我有两张（或更多张桌子）：
table1 table2 id value id value2 1 a 1 b 2 c 3 d 3 e 4 f
I have used this query to get my join:
我已经使用这个查询来获得我的加入：
SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table1.`id`=table2.`id`
UNION
SELECT *
FROM table1
RIGHT OUTER JOIN table2
ON table1.`id`=table2.`id`
to get:
要得到：
id value1 id value2 1 a 1 b 2 c NULL NULL 3 e 3 d NULL NULL 4 f
My problem is that I don't manage to simultaneously collapse the two id columns into one column to get this:
我的问题是我无法同时将两个 id 列折叠为一列以获取此信息：
id value1 value2 1 a b 2 c NULL 3 e d 4 NULL f
Any suggestions on how to do it?
关于如何做到这一点的任何建议？
采纳答案by OMG Ponies
Use:
用：
SELECT t1.id,
t1.value,
t2.value2
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.id
UNION
SELECT t2.id,
t1.value,
t2.value2
FROM TABLE1 t1
RIGHT JOIN TABLE2 t2 ON t2.id = t1.id
The UNIONoperator removes row/record duplicates, so you have to define/list the columns appropriately.
该UNION操作者除去行/记录重复的，所以你必须列适当地定义/列表。
Scripts:
脚本：
DROP TABLE IF EXISTS `example`.`table1`;
CREATE TABLE `example`.`table1` (
`id` int(10) unsigned NOT NULL default '0',
`value` varchar(45) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO table1 VALUES (1, 'a'), (2, 'c'), (3, 'e');
DROP TABLE IF EXISTS `example`.`table2`;
CREATE TABLE `example`.`table2` (
`id` int(10) unsigned NOT NULL default '0',
`value2` varchar(45) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO table2 VALUES (1, 'b'), (3, 'd'), (4, 'f');
Edit: Fixed line above
编辑：上面的固定线
回答by Beau
SELECT
COALESCE(t1.id, t2.id) as id,
t1.value1,
t2.value2
FROM table1 t1
FULL JOIN table2 t2 ON t1.id = t2.id;
回答by SOlson
For what I think you are trying to do, I would suggest using a FULL OUTER JOIN instead:
对于我认为您正在尝试做的事情，我建议改用 FULL OUTER JOIN ：
SELECT ISNULL(t1.id, t2.id) AS id, t1.value1, t2.value2
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t1.id = t2.id
