FULL OUTER JOIN将表与PostgreSQL合并

FULL OUTER JOIN将表与PostgreSQL合并

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

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)