MYSQL表合并

Question

是否存在按列C合并这两个表的问题？

表1:

A B C
a b 1
c d 2
e f 3

表2:

C D E
1 G H
4 I J
5 K L

合并：

A B C D E
a b 1 G H
c d 2 0 0
e f 3 0 0
0 0 4 I J
0 0 5 K L

我认为联接函数不起作用，因为它不会让您得到C列的所有5个结果？

Answer 1

这个在我的测试中起了作用：

(SELECT t.a, t.b, t.c, COALESCE(t2.d, 0), COALESCE(t2.e, 0)
    FROM t 
    LEFT JOIN t2 ON t.c = t2.c) 
UNION 
(SELECT 0, 0, t2.c, t2.d, t2.e
    FROM t2 
    LEFT JOIN t ON t2.c = t.c 
    WHERE t.c IS NULL)

测试数据：

Table 1
a  b  c
1  0  1
2  2  2

Table 2
c  d  e
2  0  2
3  0  3

结果

a  b  c  d  e
1  0  1  0  0
2  0  2  0  2
0  0  0  3  3

Answer 2

这是执行全外连接的最佳示例，但MySQL (AFAIK)无法做到这一点：

样本数据：

create table tab1 (A char(1), B char(1), C int);
insert into tab1
select 'a', 'b', 1 union all
select 'c', 'd', 2 union all
select 'e', 'f', 3;

create table tab2 (C int, D char(1), E char(1));
insert into tab2
select 1, 'G', 'H' union
select 4, 'I', 'J' union
select 5, 'K', 'L';

Full outer join应该是这样的：

select 
    coalesce(t1.A, '0') as A, 
    coalesce(t1.B, '0') as B, 
    coalesce(t1.C, t2.C) as C, 
    coalesce(t2.D, '0') as D, 
    coalesce(t2.E, '0') as E
from tab1 t1
full outer join tab2 t2 on t1.c = t2.c;

结果( Server)：

A    B    C           D    E
---- ---- ----------- ---- ----
a    b    1           G    H
c    d    2           0    0
e    f    3           0    0
0    0    4           I    J
0    0    5           K    L

解决办法是做union of left join和right join

select coalesce(t1.A, '0') as A, 
    coalesce(t1.B, '0') as B, 
    coalesce(t1.C, t2.C) as C, 
    coalesce(t2.D, '0') as D, 
    coalesce(t2.E, '0') as E
from tab1 t1
left join tab2 t2 on t1.c = t2.c
union
select coalesce(t1.A, '0') as A, 
    coalesce(t1.B, '0') as B, 
    coalesce(t1.C, t2.C) as C, 
    coalesce(t2.D, '0') as D, 
    coalesce(t2.E, '0') as E
from tab1 t1
right join tab2 t2 on t1.c = t2.c;

结果(MySQL)：

A   B   C   D   E
--- --- --- --- --- 
a   b   1   G   H
c   d   2   0   0
e   f   3   0   0
0   0   4   I   J
0   0   5   K   L

Answer 3

您希望使用UNION而不是JOIN。

有关一个很好的解释，请参见W3schools。