php - 如何在SQL中合并不同表的数据

Question

这个问题已经有答案了:

How can I do a FULL OUTER JOIN in MySQL? (15 个回答)

已关闭 7 年前。

我有3个不同的表，我想合并不同表中相同发票号的数据

我尝试了一些代码，但有不同的输出。我的示例表

表1

表2

表3

我的查询和输出

我的期望输出

Answer 1

首先创建一个 View 来混合表1和表2

CREATE VIEW `view 1 2` AS
  SELECT `table 1`.invoice_no, `table 1`.shoe_brand, `table 2`.clothing_brand
    FROM `table 1` LEFT  JOIN `table 2`
    ON `table 1`.invoice_no = `table 2`.invoice_no
UNION
  SELECT `table 2`.invoice_no, `table 1`.shoe_brand, `table 2`.clothing_brand
    FROM `table 1` RIGHT JOIN `table 2`
    ON `table 1`.invoice_no = `table 2`.invoice_no;

然后创建一个 View 以混合“ View 1 2”与“表 3”

CREATE VIEW `view 1 2 3` AS
  SELECT `view 1 2`.invoice_no, `view 1 2`.shoe_brand, `view 1 2`.clothing_brand, `table 3`.watch_brand
    FROM `view 1 2` LEFT  JOIN `table 3`
    ON `view 1 2`.invoice_no = `table 3`.invoice_no
UNION
  SELECT `table 3`.invoice_no, `view 1 2`.shoe_brand, `view 1 2`.clothing_brand, `table 3`.watch_brand
    FROM `view 1 2` RIGHT JOIN `table 3`
    ON `view 1 2`.invoice_no = `table 3`.invoice_no

现在是一个简单的 SELECT * FROM view 1 2 3;返回您的结果，并将工作留给 MySQL。

+------------+------------+----------------+-------------+
| invoice_no | shoe_brand | clothing_brand | watch_brand |
+------------+------------+----------------+-------------+
|          1 | Nike       | GAP            | Omega       |
|          2 | Addidas    | OLD NAVY       | NULL        |
|          3 | Sperry     | NULL           | NULL        |
|          5 | NULL       | Puma           | Seiko       |
|          4 | NULL       | NULL           | Casio       |
+------------+------------+----------------+-------------+