我得到以下错误:
Multiple attributes named business_id in reportAttributeReferenceAmbiguous; p.business_id, s.business_id
来自SPARK/HIVE查询:
SELECT
business_id,
p.p1,
p.p2,
s.s1,
s.s2
FROM p_table p
FULL OUTER JOIN s_table s
ON p.business_id = s.business_id
由于我使用的是FULL OUTER JOIN
,所以business_id
可以是p.business_id
或s.business_id
,所以我没有指定要使用哪一个。(我认为一些行可能只有p.business_id
,而其他一些行可能只有s.business_id
,我理解错了吗?(
所以我想知道在上面的查询中选择business_id
的正确方法是什么?谢谢
使用
coalesce(p.business_id, s.business_id) as business_id
或
nvl(p.business_id, s.business_id) as business_id
或者CASE语句,如下所示:https://stackoverflow.com/a/37744071/2700344
如果查询中的两个表中都有business_id
,那么您选择了business_id,但没有提及来自哪个表。
SELECT
business_id,
p.p1,
p.p2,
s.s1,
s.s2
FROM p_table p
FULL OUTER JOIN s_table s
ON p.business_id = s.business_id
上述查询应更改为
SELECT
p.business_id AS pBI, s.business_id AS sBI,
p.p1,
p.p2,
s.s1,
s.s2
FROM p_table p
FULL OUTER JOIN s_table s
ON p.business_id = s.business_id
我没有检查,但你可以试试。