sql - 我很难编写 HiveQL Join

Question

我很确定这个问题已经被问到，但无法让我的搜索查询返回答案。我有两张桌子

**Table Online**
Col1   Col2    Score   |
a      b       1       |
a      c       2       |
a      d       3       |
f      e       4       |

**Table Offline**
Col1   Col2    Score   |
a      m       10      |
a      c       20      |
a      d       30      |
t      k       40      |


**Table Output**
Col1  Col2  Online.Score  Offline.Score    |
a     c     2             20               |    
a     d     3             30               |
a     b     1                              |
a     m                   10               |

标签： sqljoinhivehiveql

Answer 1

您可以使用以下命令执行此操作full join：

select coalesce(onl.col1, ofl.col1) as col1,
       coalesce(onl.col2, ofl.col2) as col2,
       onl.score, ofl.score
from (select onl.*
      from online onl
      where onl.col1 = 'a'
     ) onl full join
     (select ofl.*
      from offline ofl
      where ofl.col1 = 'a'
     ) ofl
     on onl.col1 = ofl.col1 and onl.col2 = ofl.col2;

过滤很棘手full join，这就是使用子查询的原因。