1. 問題描述
在Hive中（其他類似SQL，比如PostgreSQL可能也存在此問題），當對多張表（3張及以上）進行full join時，會存在每張表的主鍵都是唯一，但當full join后，會發(fā)現(xiàn)主鍵可能有重復。
2. 問題復現(xiàn)
2.1. 插入數(shù)據(jù)
with temp1 as (
select '1' as id ,'張三' as name
union all
select '2' as id ,'李四' as name
union all
select '3' as id ,'王五' as name
),
temp2 as (
select '1' as id ,'深圳' as city
union all
select '3' as id ,'北京' as city
union all
select '4' as id ,'上海' as city
),
temp3 as (
select '1' as id ,'5000' as salary
union all
select '4' as id ,'10000' as salary
union all
select '5' as id ,'12000' as salary
)
2.2. 查詢SQL以及問題
select
coalesce(a.id, b.id, c.id) as id
, a.name
, b.city
, c.salary
from temp1 as a
full join temp2 as b
on a.id = b.id
full join temp3 as c
on a.id = c.id
當執(zhí)行如上查詢SQL時，會發(fā)現(xiàn)其中 id = 4 的數(shù)據(jù)有重復，如下圖所示：
3. 問題原因
之所以會出現(xiàn)這樣的問題，是因為是以a表為主表，但a表中只有 id 為 1、2、3 的數(shù)據(jù)，但在b表中有id為4，c表中也有id為4，此時full join時，a表會以空值和b表、c表中id為4的數(shù)據(jù)join，這樣關聯(lián)后的表中就會出現(xiàn)2條id為4的數(shù)據(jù)；
4. 問題解決
在后續(xù)的表full join時，不單單使用第一張表的主鍵full join（因為是full join，所以肯定會存在第一張表為null，而其他表有值的數(shù)據(jù)），而使用 coalesce 方法對所有前面已經(jīng)full join了的主鍵進行條件關聯(lián)，如下代碼：
方法1：
select
coalesce(a.id, b.id, c.id) as id
, a.name
, b.city
, c.salary
from temp1 as a
full join temp2 as b
on a.id = b.id
full join temp3 as c
on coalesce(a.id, b.id) = c.id
結果如下：
方法2：
select
temp.id
, temp.name
, temp.city
, c.salary
from
(select
coalesce(a.id, b.id) as id
, a.name
, b.city
from temp1 as a
full join temp2 as b
on a.id = b.id) temp
full join
temp3 as c
on temp.id = c.id
方法3：文章來源：http://www.zghlxwxcb.cn/news/detail-599612.html
select
temp.id
,temp1.name
,temp2.city
,temp3.salary
from
(select id
from
(select
id
from temp1
union all
select
id
from temp2
union all
select
id
from temp3
) tt
group by id
) temp
left join temp1
on temp.id = temp1.id
left join temp2
on temp.id = temp2.id
left join temp3
on temp.id = temp3.id
文章來源地址http://www.zghlxwxcb.cn/news/detail-599612.html
到了這里，關于Full Join多個表與Union All多個表的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！
