mysql - SQL识别两张表不同数据

问题描述

有两张1W行左右的表，需要查询有差异的行，现在的代码如下：

SELECT number, versionFROM ( SELECT a.number, b.version FROM a UNION ALL SELECT b.number, b.version FROM b) tbGROUP BY number, versionHAVING COUNT(*) = 1ORDER BY number

但是问题来了，以上代码只能查询出不同的行，但是没法显示a表中有的b表中没有的,b表中有的a表中没有的，有没有办法可以在第3列标识出来？

问题解答

按楼主意思，单表中number和version是不会重复的，两张表的number和version建一个复合索引，然后执行以下sql

SELECT a.number, a.version,’from_a’FROM awhere not exists (SELECT 1 FROM b where a.number=b.number and a.version=b.version)union allSELECT b.number, b.version,’from_b’FROM bwhere not exists (SELECT 1 FROM a where a.number=b.number and a.version=b.version)ORDER BY number;或者SELECT a.number, a.version,’from_a’from a left join b on a.number=b.number and a.version=b.versionwhere b.id is nullunion allSELECT b.number, b.version,’from_b’from a right join b on a.number=b.number and a.version=b.versionwhere a.id is nullORDER BY number;下面这个效率可能会差点回答2：

试试full join ... where a is null or b is null。比如用Postgres：

select case when a.n is null then b.n else a.n end as n, case when a.n is null then b.v else a.v end as v, case when a.n is null then ’b’ else ’a’ end as srcfrom (values(1, 2), (2, 3), (3, 4)) as a(n, v) full join (values(6, 7), (2, 3), (3, 9)) as b(n, v) using (n, v)where a.n is null or b.n is null

结果：

n | v | src---+---+----- 1 | 2 | a 3 | 4 | a 3 | 9 | b 6 | 7 | b(4 行记录)