与PostgreSQL合并表

Question

这个问题的标题不准确，但我不知道如何总结。如果可以的话，请随时重新编写！

这是两个表的摘录：

桌子 table_a

code  | year   | nb_a
------+--------+------
  A1  |   2017 |    1      
  A2  |   2012 |    2
  A3  |   2014 |    2

桌子 table_b

code  | year   | nb_b
------+--------+------
  A1  |   2013 |    1
  A1  |   2014 |    1
  A2  |   2012 |    1

我需要合并这些表以获得此输出：

code  | year   | nb_a | nb_b | total
------+--------+------+------+-------
  A1  |   2013 |    0 |    1 |     1
  A1  |   2014 |    0 |    1 |     1
  A1  |   2017 |    1 |    0 |     1
  A2  |   2012 |    2 |    1 |     3
  A3  |   2014 |    2 |    0 |     2

我找不到正确的查询。我需要下面类似的内容（我知道它不能完成工作），但是如何将所有代码和年份合并到一个表中，因为代码和年份在两个表中都没有重复…

SELECT 
  code,
  "year",
  table_a.nb_a,
  table_b.nb_b,
  table_a.nb_a + table_b.nb_b AS total

FROM table_a, table_b
WHERE table_a.code = table_b.code;

以下是用于快速创建上述表的SQL脚本：

CREATE TABLE public.table_a (code TEXT, "year" INTEGER, nb_a INTEGER);
CREATE TABLE public.table_b (code TEXT, "year" INTEGER, nb_b INTEGER);

INSERT INTO public.table_a (code, "year", nb_a) VALUES (A1, 2017, 1), (A2, 2012, 2), (A3, 2014, 2);
INSERT INTO public.table_b (code, "year", nb_b) VALUES (A1, 2013, 1), (A1, 2014, 1), (A2, 2012, 1);

Answer 1

宇很可能是在寻找对FULL OUTER JOIN

SELECT
  coalesce(a.code,b.code),
  coalesce(a."year",b.year),
  coalesce(a.nb_a,0),
  coalesce(b.nb_b,0),
  coalesce(a.nb_a,0) + coalesce(b.nb_b,0) AS total
FROM table_a a full outer join table_b b on a.code = b.code and a.year = b.year;
 coalesce | coalesce | coalesce | coalesce | total
----------+----------+----------+----------+-------
        1 |     2013 |        0 |        1 |     1
        1 |     2014 |        0 |        1 |     1
        1 |     2017 |        1 |        0 |     1
        2 |     2012 |        2 |        1 |     3
        3 |     2014 |        2 |        0 |     2
(5 rows)