mysql比较两个表找出不匹配的记录

Question

查看这两个示例表:

表1:

ID FN LN 电子邮件

1 A B C

2 D E F

3 G H I

表 2:

ID FN LN 电子邮件

1 A Z Z C

2 D E E F

3 G H H I

我想要的输出与表1不相等，并且仅显示更改之一。我尝试了很多方法，但没有结果。

输出:

ID 岭南

---- ---

1 Z

Answer 1

drop table if exists t1;
create table t1(id int,  FN varchar(1), LN varchar(1), Email varchar(1));
insert into t1 values
(1 ,  'A',   'B',     'C'),
(2 ,  'D' ,  'E',     'F'),
(3 ,  'G' ,  'H',     'I');

drop table if exists t2;
create table t2(id int,  FN varchar(1), LN varchar(1), Email varchar(1));
insert into t2 values
(1 ,  'A',   'Z',     'C'),
(2 ,  'D' ,  'E',     'F'),
(3 ,  'k' ,  'H',     'l');

select t1.id,
        case when t1.fn <> t2.fn then 'FN'
        when t1.ln <> t2.ln then 'LN'
        when t1.email <> t2.email then 'email'
        END AS FieldChanged,
        case when t1.fn <> t2.fn then t2.fn
        when t1.ln <> t2.ln then t2.ln
        when t1.email <> t2.email then t2.email
        END AS FieldChangedTo

from t1
join t2 on t1.id = t2.id

where t1.fn <> t2.fn or
        t1.ln <> t2.ln or
        t1.email <> t2.email 

结果

+------+--------------+----------------+
| id   | FieldChanged | FieldChangedTo |
+------+--------------+----------------+
|    1 | LN           | Z              |
|    3 | FN           | k              |
+------+--------------+----------------+
2 rows in set (0.00 sec)