sql - FULL OUTER JOIN将表与PostgreSQL合并

在this post,之后，当我将@Vao Tsun给出的答案应用到一个更大的数据集时，我仍然有一个问题，这个数据集这次是4个表，而不是上面提到的相关文章中的2个表。
以下是我的数据集：

-- 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值应该在同一行上，并且total列显示一个4值。

再次感谢您的帮助！
下面是可以轻松创建上表的SQL脚本：

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

最佳答案

受到其他答案的启发，但也许更有条理：

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)