在这篇文章之后，)当我将@VaoTsun给出的答案应用于更大的数据集时，我仍然遇到一个问题，这次更大的数据集由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);