sql - PostgreSQL，多个表上的 FULL OUTER JOIN(所有 "combinations")

Question

总的来说，我对 PostgrSQL 和 SQL 还很陌生。我正在尝试使用 FULL OUTER JOIN 函数根据各自的 11 列连接 11 个表。您可以在下面找到我的代码:

SELECT * FROM _0_general_view
FULL OUTER JOIN _1_foundation_view
ON _0_general_view.concatenate_0_general = _1_foundation_view.concatenate_1_foundation
FULL OUTER JOIN _1_plinth_view
ON _0_general_view.concatenate_0_general = _1_plinth_view.concatenate_1_plinth
FULL OUTER JOIN _1_rc_beams_columns_view
ON _0_general_view.concatenate_0_general = _1_rc_beams_columns_view.concatenate_1_rc_beams_columns
FULL OUTER JOIN _1_vertical_members_view
ON _0_general_view.concatenate_0_general = _1_vertical_members_view.concatenate_1_vertical_members
FULL OUTER JOIN _2_floor_view
ON _0_general_view.concatenate_0_general = _2_floor_view.concatenate_2_floor
FULL OUTER JOIN _2_horizontal_bands_view
ON _0_general_view.concatenate_0_general = _2_horizontal_bands_view.concatenate_2_horizontal_bands
FULL OUTER JOIN _2_rc_beams_columns_view
ON _0_general_view.concatenate_0_general = _2_rc_beams_columns_view.concatenate_2_rc_beams_columns
FULL OUTER JOIN _2_vertical_members_view
ON _0_general_view.concatenate_0_general = _2_vertical_members_view.concatenate_2_vertical_members
FULL OUTER JOIN _2_walls_view
ON _0_general_view.concatenate_0_general = _2_walls_view.concatenate_2_walls
FULL OUTER JOIN _3_roof_view
ON _0_general_view.concatenate_0_general = _3_roof_view.concatenate_3_roof
;

在上面的代码中，我假设我的表结构如下:

表 1(_0_general_view)

╔══════════╦═════════╦═════════╗
║   col_1  ║  col_2  ║  col_3  ║
╠══════════╬═════════╬═════════╣
║     a    ║ 3.5046  ║   Jan   ║
║     b    ║ 3.7383  ║   Mar   ║
║     c    ║ 3.9719  ║   Jul   ║
║     d    ║ 6.1915  ║   Feb   ║
╚══════════╩═════════╩═════════╝

表 2(_1_plinth_view)

╔══════════╦═════════╦═════════╗
║   col_4  ║  col_5  ║  col_6  ║
╠══════════╬═════════╬═════════╣
║     a    ║ 2.8846  ║   Dec   ║
║     d    ║ 5.2244  ║   Aug   ║
╚══════════╩═════════╩═════════╝

表 n (_3_xxxx_view)

╔══════════╦═════════╦═════════╗
║   col_7  ║  col_8  ║  col_9  ║
╠══════════╬═════════╬═════════╣
║     b    ║ 1.2365  ║   May   ║
║     c    ║ 2.5432  ║   Sep   ║
║     d    ║ 8.1515  ║   Oct   ║
╚══════════╩═════════╩═════════╝

换句话说，我假设 col_1(在第一个表/ View 中名为 concatenate_0_general，名为 _0_general_view)包含我需要加入的所有记录，但是如果这不是真的怎么办？

我找不到一种方法来考虑我的 11 个表中所有可能的组合。这在某种程度上是可能的吗？

如果是这样，考虑到每个表平均有 150 列，并且每个表中的行数可能在 10000 左右，您认为这会是一个很长的过程吗？

编辑:我们可能不得不检索更少的记录，比方说大约 300-400，因为我们将添加一个 WHERE 子句来按日期过滤它们。

Answer 1

如果 join 列具有相同的名称，您可以使用 using:

select . . .
from a join
     b
     on (id) join
     c
     on (id) . . .;

在你的情况下，union all 和聚合是最好的方法——假设你最终将每个“id”一行:

select id, max(col1) as col1, max(col2) as col2, max(col3) as col3,
       max(col4) as col4, max(col5) as col5, max(col6) as col6
from ((select concatenate_0_general as id, col1, col2, NULL as col3, NULL as col4, NULL as col5, NULL as col6
       from _0_general_view
      ) union all
      (select concatenate_1_foundation, NULL, NULL, col3, col4, NULL, NULL
       from _1_foundation_view
      ) union all
      (select concatenate_1_plinth, NULL, NULL, NULL, NULL, col5, col6
       from _1_plinth_view
      )
     ) x
group by id;

如果这些都不起作用，那么您可以构造一个完整的 ID 列表并使用 left join:

select . . .
from ((select concatenate_0_general as id from _0_general_view
      ) union -- on purpose to remove duplicates
      (select concatenate_1_foundation from _1_foundation_view
      ) union
      (select concatenate_1_plinth from _1_plinth_view
      ) union
      . . .
     ) ids left join
     _0_general_view gv
     on gv.concatenate_0_general = ids.id left join
     . . .