hadoop - 有条件地加入 Hive

Question

我想在 Hive 中执行以下查询 -

select * from supp a inner join trd_acct b
on
(a.btch_id = 11170 AND a.btch_id = b.btch_id)
OR (a.btch_id = 11164 AND a.supp_id = b.supp_id)

但出现错误 -

FAILED: SemanticException [Error 10019]: Line 3:1 OR not supported in JOIN currently 'supp_id'

Answer 1

你可以用 UNION 解决这个问题:

select * from supp a inner join trd_acct b
 on a.btch_id = 11170 AND a.btch_id = b.btch_id
UNION ALL
select * from supp a inner join trd_acct b
 on a.btch_id = 11164 AND a.supp_id = b.supp_id

或者你可以试试 CASE EXPRESSION :

select * from supp a
inner join trd_acct b
 on CASE WHEN a.btch_id = 11164 THEN a.supp_id 
         WHEN a.btch_id = 11170 THEN a.btch_id END
  = CASE WHEN a.btch_id = 11164 THEN b.supp_id
         WHEN a.btch_id = 11170 then b.btch_id END