如何联接两个表以获得以下结果？

若虚春江

我想加入两个表。
TABLE_A
GROUP0    GROUP1    SUM_A
---------------------------
01          A       100
01          B       200
04          D       700
TABLE_B
GROUP0    GROUP1    SUM_B
---------------------------
01                   300
01          A       350
02          B       400
03          C       500
如何联接表以获得以下结果？
GROUP0    GROUP1       SUM_A          SUM_B
------------------------------------------------
01                            0          300
01       A             100          350
01       B             200             0
02       B                0          400
03       C                0          500
04       D             700             0

解决方案:


            您希望所有内容都在第二个表中，然后匹配group0第一个表中的行或新行。
我认为这是join逻辑：
select coalesce(t1.group0, t2.group0) as group0,
   coalesce(t1.group1, t2.group1) as group1,
   t1.sum_a, t2.sum_b
from table1 t1 full outer join
   table2 t2
   on t1.group0 = t2.group0
where (t2.group0 is not null and (t1.group1 = t2.group1 or t1.group0 is null)) or
   t2.group0 is null;
使用union all以下命令更容易实现此逻辑：
select t2.group0, t2.group1, t1.sum_a, t2.sum_b
from table2 t2 left join
   table1 t1
   on t2.group0 = t1.group0 and t2.group1 = t1.group1
union all
select t1.group1, t1.group1, t1.suma, 0
from table1
where not exists (select 1 from table2 t2 where t2.group0 = t1.group0);
编辑：
修改后的问题与原始问题完全不同。那很简单full outer join：
select coalesce(t1.group0, t2.group0) as group0,
   coalesce(t1.group1, t2.group1) as group1,
   coalesce(t1.sum_a, 0) as sum_a, coalesce(t2.sum_b, 0) as sum_b
from table1 t1 full outer join
   table2 t2
   on t1.group0 = t2.group0  and t1.group1 = t2.group1;

如何联接两个表以获得以下结果？

若虚春江 LV1