FULL OUTER JOIN 将表与 PostgreSQL 合并

下列的这个帖子， https://stackoverflow.com/q/44562726/2508539当我应用给出的答案时，我仍然遇到问题@Vao Tsun https://stackoverflow.com/users/5315974/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值应该与 a 在同一行total列显示4 value.

再次感谢您的帮助！

以下是轻松创建上述表格的 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; 

Giving:

 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)