MySQL:完全外部联接-如何合并一列? [英] MySQL: FULL OUTER JOIN - How do I merge one column?

问题描述

我对MySQL中的FULL OUTER JOIN有疑问.我有两个(或更多表):

I have a question regarding a FULL OUTER JOIN in MySQL. I have two (or more tables):

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`

获得:

id   value1  id   value2 
1    a       1    b
2    c       NULL NULL
3    e       3    d
NULL NULL    4    f

我的问题是我无法同时将两个id列折叠为一列来获取此信息:

My problem is that I don't manage to simultaneously collapse the two id columns into one column to get this:

id   value1  value2 
1    a       b
2    c       NULL
3    e       d
4    NULL    f

有关如何操作的任何建议?

Any suggestions on how to do it?

推荐答案

使用:

    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

UNION运算符会删除行/记录重复项，因此您必须适当地定义/列出列.

The UNION operator removes row/record duplicates, so you have to define/list the columns appropriately.

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');

上方固定行

这篇关于MySQL:完全外部联接-如何合并一列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！