与PostgreSQL合并表

本文介绍了与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可能是在中查找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)