与PostgreSQL合并表 [英] Merge tables with PostgreSQL

问题描述

此问题的标题不准确，但我不知道如何对其进行总结.如果可以的话，请随时重新编写！

The title of this question is not accurate but I didn't know how to summarize it. Please feel free to write it again if you can!

以下是两个表的摘录:

表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

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

I need to merge these tables in order to get this output:

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

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

I can't find the correct query. I would need something like below (I know it doesn't do the job) but how to get all codes and years merged in one table as codes and years are not all repeated in both tables...

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脚本:

---

Here are the SQL scripts to create the above tables rapidly:

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);

推荐答案

yu可能是

这篇关于与PostgreSQL合并表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！