postgresql - 基于单列postgresql比较多个表

Question

我有 3 个表 table1、table2、table3。每个表都具有相同的结构。我想在一列的基础上比较它们的行。

表的结构

表1

country_code  country_name rate vendor
91            india        2.0   abc
92            pak          1.0   abc

表2

country_code  country_name rate vendor
91            india        2.1   abc1
92            pak          1.1   abc1
93            afgan        1.1   abc1

表3

country_code  country_name rate vendor
91            india        2.2   abc2
92            pak          1.2   abc2
93            afgan        1.2   abc2
880           bang         1.2   abc2


desired output is 
    country_code  country_name rate vendor rate vendor   rate  vendor
    91            india        2.0   abc   2.1   abc1     2.2    abc2
    92            pak          1.0   abc   1.1   abc1     1.2    abc2
    93            afgan                    1.1   abc1     1.2    abc2
    880           bang                                    1.2    abc2

我尝试了完全外部连接，但没有得到想要的结果。我使用了这个查询

SELECT * 
FROM table1 a 
   FULL OUTER JOIN table2 b ON a.country_code=b.country_code 
   FULL OUTER JOIN table3 c ON c.country_code=a.country_code ;  

上面查询的结果是

91  india  2   91  india  2.1   91  india   2.2 
92  pak    1   92   pak   1.1   92  pak     1.2
               93  afgan  1.1 
                                880 bang    1.2
                                93  afgan   1.2

但我想要这样

91  india  2   91  india  2.1   91  india   2.2 
92  pak    1   92   pak   1.1   92  pak     1.2
               93  afgan  1.1   93  afgan   1.2
                                880 bang    1.2

Answer 1

这应该有效:

select * from
(
select distinct  t3.country_code,
   t3.country_name,
   t1.rate,
   t1.vendor,
   t2.rate,
   t2.vendor,
   t3.rate,
   t3.vendor
   from (select * from table1 t1 union
         select * from table2 t2 union
         select * from table3 t3
   ) allk left join table1 t1 on allk.country_code = t1.country_code 
    left join table2 t2  on allk.country_code = t2.country_code
    left join table3 t3 on allk.country_code = t3.country_code
   ) a
   order by case
       when a.country_code like '9%' then 1
       else 2
     end nulls last;