如何联接两个表以获得以下结果? [英] How to join two tables to get the following result?

问题描述

我想加入两个表.

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

如何联接表以获得以下结果?

How to join the tables to get the following result?

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.

You want everything in the second table and then matching rows or new group0 in the first table.

我认为这是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:

The modified question is quite different from the original. That is a simple 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;

这篇关于如何联接两个表以获得以下结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！