在具有许多表的Postgres上加入解决方案 [英] JOIN solution on Postgres with many tables

问题描述

由Acqua Data Studio管理的Postgres数据库上的小问题:

Little issue on a Postgres database, being managed in Acqua Data Studio:

我需要一种创建SELECT的解决方案，该SELECT将许多视图连接到一个表中.有10多个视图.一个中心可能有许多ID，而一个ID可能有许多中心.因此，主表的PK将是中央ID.

I need a solution to create a SELECT that concatenates many views into one table. There are more than 10 views. One central may have many IDs, and one ID may have many centrals. So, the main table's PK would be the central-ID thing.

一个适用的示例(假设仅存在3个表)，如下所示:

One example that applies (assuming that exist only 3 tables), as follows:

VIEW1:

 central   |   ID   |    MAP
--------------------------------
    A      |   01   |    MAP1      
    A      |   02   |    MAP1
    A      |   03   |     -
    B      |   01   |    MAP3
    B      |   02   |     -
    C      |   01   |     -

VIEW2:

 central   |   ID   |    CAMEL
--------------------------------
    A      |   01   |    CAP1
    B      |   01   |    CAP1
    B      |   02   |    CAP2
    B      |   03   |    CAP3
    D      |   01   |     -  

VIEW3:

 central   |   ID   |    NRRG
--------------------------------
    A      |   01   |   NRRG2
    B      |   01   |     -
    C      |   01   |     -
    D      |   05   |   NRRG1

..

结果表:

 central   |   ID   |    MAP    |  CAMEL  |  NRRG
--------------------------------------------------
    A      |   01   |    MAP1   |   CAP1  |  NRRG2
    A      |   02   |    MAP1   |         |  
    A      |   03   |     -     |         |
    B      |   01   |    MAP3   |   CAP1  |    -
    B      |   02   |     -     |   CAP2  |
    B      |   03   |           |   CAP3  |
    C      |   01   |     -     |         |    -
    D      |   01   |           |    -    |
    D      |   05   |           |         |  NRRG1

出现在10多个表中的任何一个中的中心ID都需要输入到串联表中.
我当然不在乎那些没有与其他列对应的列上的空白...重要的是，在每个ID中心行中，获取其他表上存在的每个对应值.PS:-"是一个值！

Any central-ID that appears in any of the 10+ tables need to enter in the concatenated table.
I surely don't care about blank spaces on those columns that don't have a correspondent into the other columns... The important thing is to get, in each ID-central row every correspondent value that is present on the other tables. PS: "-" is a value!

我想到了"FULL OUTER JOIN"，但是在手册中引用这些内容时，我看不到完美的方法...

I thought about a FULL OUTER JOIN, but whatching the references in manual I can't see a way to do it perfectly...

谢谢，伙计们！

推荐答案

SQL提琴

select central, id, map, camel, nrrg
from
    v1
    full outer join
    v2 using (central, id)
    full outer join
    v3 using (central, id)
order by central, id
;
 central | id | map  | camel | nrrg  
---------+----+------+-------+-------
 A       |  1 | MAP1 | CAP1  | NRRG2
 A       |  2 | MAP1 |       | 
 A       |  3 |      |       | 
 B       |  1 | MAP3 | CAP1  | 
 B       |  2 |      | CAP2  | 
 B       |  3 |      | CAP3  | 
 C       |  1 |      |       | 
 D       |  1 |      |       | 
 D       |  5 |      |       | NRRG1

这篇关于在具有许多表的Postgres上加入解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！