FULL OUTER JOIN将表与PostgreSQL合并

本文介绍了FULL OUTER JOIN将表与PostgreSQL合并的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在这篇文章之后，当我应用@Vao Tsun 到了更大的数据集，这次是4张表，而不是上面提到的相关文章中的2张表.

Following this post, I still have an issue when I apply the answer given by @Vao Tsun to a bigger dataset made this time of 4 tables instead of 2 tables in the related post mentionned above.

这是我的数据集:

-- Table 'brcht' (empty) insee | annee | nb -------+--------+----- -- Table 'cana' insee | annee | nb -------+--------+----- 036223 | 2017 | 1 086001 | 2016 | 2 -- Table 'font' (empty) insee | annee | nb -------+--------+----- -- Table 'nr' insee | annee | nb -------+--------+----- 036223 | 2013 | 1 036223 | 2014 | 1 086001 | 2013 | 1 086001 | 2014 | 2 086001 | 2015 | 4 086001 | 2016 | 2

这是查询:

SELECT COALESCE(brcht.insee, cana.insee, font.insee, nr.insee) AS insee, COALESCE(brcht.annee, cana.annee, font.annee, nr.annee) AS annee, COALESCE(brcht.nb,0) AS brcht, COALESCE(cana.nb,0) AS cana, COALESCE(font.nb,0) AS font, COALESCE(nr.nb,0) AS nr, COALESCE(brcht.nb,0) + COALESCE(cana.nb,0) + COALESCE(font.nb,0) + COALESCE(nr.nb,0) AS total FROM public.brcht FULL OUTER JOIN public.cana ON brcht.insee = cana.insee AND brcht.annee = cana.annee FULL OUTER JOIN public.font ON cana.insee = font.insee AND cana.annee = font.annee FULL OUTER JOIN public.nr ON font.insee = nr.insee AND font.annee = nr.annee ORDER BY COALESCE(brcht.insee, cana.insee, font.insee, nr.insee), COALESCE(brcht.annee, cana.annee, font.annee, nr.annee);

结果，我仍然有两行，而不是insee='086001'的一行(见下文).我需要为每个insee获取一行，在此示例中，两个2值应该在同一行上，并且显示4值的total列.

In the result, I still have two rows instead of one for insee='086001' (see below). I need to get one row per insee and in this example, the two 2 values should be on the same line with a total column showing a 4 value.

再次感谢您的帮助！

以下是可以轻松创建上述表的SQL脚本:

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

CREATE TABLE public.brcht (insee CHARACTER VARYING(10), annee INTEGER, nb INTEGER); CREATE TABLE public.cana (insee CHARACTER VARYING(10), annee INTEGER, nb INTEGER); CREATE TABLE public.font (insee CHARACTER VARYING(10), annee INTEGER, nb INTEGER); CREATE TABLE public.nr (insee CHARACTER VARYING(10), annee INTEGER, nb INTEGER); INSERT INTO public.cana (insee, annee, nb) VALUES ('036223', 2017, 1), ('086001', 2016, 2); INSERT INTO public.nr(insee, annee, nb) VALUES ('036223', 2013, 1), ('036223', 2014, 1), ('086001', 2013, 1), ('086001', 2014, 2), ('086001', 2015, 4), ('086001', 2016, 2);

推荐答案

受其他答案的启发，但组织起来可能更好:

Inspired by other answers, but perhaps better organized:

SELECT *, brcht + cana + font + nr AS total FROM (SELECT insee, annee, SUM(Coalesce(brcht.nb, 0)) brcht, SUM(Coalesce(cana.nb, 0)) cana, SUM(Coalesce(font.nb, 0)) font, SUM(Coalesce(nr.nb, 0)) nr FROM brcht full outer join cana USING (insee, annee) full outer join font USING (insee, annee) full outer join nr USING (insee, annee) GROUP BY insee, annee) t ORDER BY insee, annee;

给予:

insee | annee | brcht | cana | font | nr | total --------+-------+-------+------+------+----+------- 036223 | 2013 | 0 | 0 | 0 | 1 | 1 036223 | 2014 | 0 | 0 | 0 | 1 | 1 036223 | 2017 | 0 | 1 | 0 | 0 | 1 086001 | 2013 | 0 | 0 | 0 | 1 | 1 086001 | 2014 | 0 | 0 | 0 | 2 | 2 086001 | 2015 | 0 | 0 | 0 | 4 | 4 086001 | 2016 | 0 | 2 | 0 | 2 | 4 (7 rows)