問(wèn)題描述

有兩張1W行左右的表，需要查詢(xún)有差異的行，現(xiàn)在的代碼如下：

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

但是問(wèn)題來(lái)了，以上代碼只能查詢(xún)出不同的行，但是沒(méi)法顯示a表中有的b表中沒(méi)有的,b表中有的a表中沒(méi)有的，有沒(méi)有辦法可以在第3列標(biāo)識(shí)出來(lái)？

問(wèn)題解答

按樓主意思，單表中number和version是不會(huì)重復(fù)的，兩張表的number和version建一個(gè)復(fù)合索引，然后執(zhí)行以下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;下面這個(gè)效率可能會(huì)差點(diǎn)回答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

結(jié)果：

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