时间戳

首页 > 解决方案 > 在 SQL 中连接四个表

sql - 在 SQL 中连接四个表

问题描述

我正在尝试将四个表连接在一起，它们的数据几乎相同。

+------------------+------------------+-------------------+-------------------+--+
| tableone.letters | tabletwo.letters | tablethree.leters | tablefour.letters |  |
+------------------+------------------+-------------------+-------------------+--+
| 'a'              | 'a'              | 'a'               | 'a'               |  |
+------------------+------------------+-------------------+-------------------+--+
| 'b'              | 'b'              | 'b'               | 'e'               |  |
+------------------+------------------+-------------------+-------------------+--+
| 'c'              | 'c'              | 'c'               | 'g'               |  |
+------------------+------------------+-------------------+-------------------+--+
| 'd'              | 'd'              | 'e'               |                   |  |
+------------------+------------------+-------------------+-------------------+--+
| 'e'              | 'f'              |                   |                   |  |
+------------------+------------------+-------------------+-------------------+--+
| 'f'              | 'g'              |                   |                   |  |
+------------------+------------------+-------------------+-------------------+--+
| 'g'              | 'h'              |                   |                   |  |
+------------------+------------------+-------------------+-------------------+--+
| 'h'              | 'i'              |                   |                   |  |
+------------------+------------------+-------------------+-------------------+--+
| 'i'              |                  |                   |                   |  |
+------------------+------------------+-------------------+-------------------+--+
| 'j'              |                  |                   |                   |  |
+------------------+------------------+-------------------+-------------------+--+

SELECT DISTINCT tableone.letters, tabletwo.letters, tablethree.letters, tablefour.letters FROM querytesting.tableone
    FULL JOIN querytesting.tabletwo
    ON tableone.letters = tabletwo.letters
    FULL JOIN querytesting.tablethree
    ON tabletwo.letters = tablethree.letters
    FULL JOIN querytesting.tablefour
    ON tablethree.letters = tablefour.letters;

当我加入他们时，我得到以下结果：

+------+------+------+------+--+
| "a"  | "a"  | "a"  | "a"  |  |
+------+------+------+------+--+
| "b"  | "b"  | "b"  | null |  |
+------+------+------+------+--+
| "c"  | "c"  | "c"  | null |  |
+------+------+------+------+--+
| "d"  | "d"  | null | null |  |
+------+------+------+------+--+
| "e"  | null | null | null |  |
+------+------+------+------+--+
| "f"  | "f"  | null | null |  |
+------+------+------+------+--+
| "g"  | "g"  | null | null |  |
+------+------+------+------+--+
| "h"  | "h"  | null | null |  |
+------+------+------+------+--+
| "i"  | "i"  | null | null |  |
+------+------+------+------+--+
| "j"  | null | null | null |  |
+------+------+------+------+--+
| "k"  | null | null | null |  |
+------+------+------+------+--+
| "l"  | null | null | null |  |
+------+------+------+------+--+
| null | null | "e"  | "e"  |  |
+------+------+------+------+--+
| null | null | null | "g"  |  |
+------+------+------+------+--+

这不是我预期的结果。我希望第三列和第四列中的“e”和“g”与第一列中的“e”和“g”完美对齐。

有没有办法做到这一点？

先感谢您！

标签： sqlpostgresqljoin

解决方案

鉴于您的解释，您需要做的就是用 FULL JOIN 所有表tableone，尽管我不确定这是否是实际意图，特别是如果所有数据都只是样本。这里：

SELECT DISTINCT tableone.letters, tabletwo.letters, tablethree.letters, tablefour.letters 
  FROM tableone
      FULL JOIN tabletwo
           ON tableone.letters = tabletwo.letters
      FULL JOIN tablethree
           ON tableone.letters = tablethree.letters
      FULL JOIN tablefour
           ON tableone.letters = tablefour.letters;

看到它在这里工作：http ://sqlfiddle.com/#!17/acc44/4

推荐阅读