FULL OUTER JOIN将表与PostgreSQL合并 [英] FULL OUTER JOIN to merge tables with 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)

这篇关于FULL OUTER JOIN将表与PostgreSQL合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！