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 行記錄)