MySQL 具有多个表的 SQL 完全外部联接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17662559/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Full Outer Join with Multiple Tables
提问by user1618180
First I have 4 Tables
首先我有4张桌子
Table0, Columns: num, desc Table1, Columns: num, qty1 Table2, Columns: num, qty2 Table3, Columns: num, qty3 Table4, Columns: num, qty4
(not all num have values in qty1 or qty2 or qty3 or qty4, therefore I need a full join) and my query:
（并非所有 num 在 qty1 或 qty2 或 qty3 或 qty4 中都有值，因此我需要完全连接）和我的查询：
SELECT Table0.num, SUM(Table1.qty1 ), SUM(Table2.qty2 ), SUM(Table3.qty3 ), SUM(Table4.qty4)
FROM Table0
FULL OUTER JOIN Table1 ON Table0.num = Table1.num
FULL OUTER JOIN Table2 ON Table0.num = Table2.num
FULL OUTER JOIN Table3 ON Table0.num = Table3.num
FULL OUTER JOIN Table4 ON Table0.num = Table4.num
GROUP BY Table0.num
Somehow its returning just 1 row of data:
不知何故，它只返回 1 行数据：
num | qty1 | qty2 | qty3 | qty4 |
---------------------------------
| 100 | 20 | 77 | 969 |
But I was expecting like the example at
但我期待像在
http://www.w3schools.com/sql/sql_join_full.asp
http://www.w3schools.com/sql/sql_join_full.asp
like:
喜欢：
num | qty1 | qty2 | qty3 | qty4 | --------------------------------- 1 | 0 | 2 | 3 | 2 | 2 | 1 | 0 | 0 | 0 | 3 | 7 | 0 | 9 | 0 | 4 | 0 | 0 | 0 | 10 | 5 | 0 | 0 | 7 | 0 | 6 | 8 | 2 | 9 | 3 | 7 | 0 | 1 | 0 | 0 |
(I don't know this solves it) However I got similar to the result the box above by changing all the tables to:
（我不知道这能解决它）但是我通过将所有表更改为类似于上面的框的结果：
Table1, Columns: num, qty1, qty2, qty3, qty4 Table2, Columns: num, qty2, qty1, qty3, qty4 Table3, Columns: num, qty3, qty1, qty2, qty4 Table4, Columns: num, qty4, qty1, qty2, qty3
回答by Clockwork-Muse
You need to do one of two things (and both of these assume that Table0has all instances of num) -
您需要做两件事中的一件（并且这两件事都假设Table0具有 的所有实例num）-
If all rows are already summed for the 'leaf' tables (1 - 4), then a simple
LEFT JOIN(with aCOALESCE()in the select) will suffice - you don't even need theGROUP BY.If you need the rows summed, you're going to need to sum them beforethe join, given that otherwise multiple rows per num in different tables will cause the results to multiply.
如果所有行都已经为“叶”表（1 - 4）求和，那么一个简单的
LEFT JOIN（COALESCE()在选择中带有 a ）就足够了——你甚至不需要GROUP BY.如果您需要对行求和，则需要在连接之前对它们求和，否则不同表中每个 num 的多行将导致结果相乘。
Something like this:
像这样的东西：
SELECT Table0.num, COALESCE(Table1.qty, 0), COALESCE(Table2.qty, 0),
COALESCE(Table3.qty, 0), COALESCE(Table4.qty, 0)
FROM Table0
LEFT JOIN (SELECT num, SUM(qty1) as qty
FROM Table1
GROUP BY num) Table1
ON Table1.num = Table0.num
LEFT JOIN (SELECT num, SUM(qty2) as qty
FROM Table2
GROUP BY num) Table2
ON Table2.num = Table0.num
LEFT JOIN (SELECT num, SUM(qty3) as qty
FROM Table3
GROUP BY num) Table3
ON Table3.num = Table0.num
LEFT JOIN (SELECT num, SUM(qty4) as qty
FROM Table4
GROUP BY num) Table4
ON Table4.num = Table0.num
(working SQLFiddle example)
（工作SQLFiddle 示例）
回答by AaronLS
There are no matches between the num columns in each table and thus you are getting the outer records. As when there is no match on the match key, the records are shown with that column as null.
每个表中的 num 列之间没有匹配项，因此您将获得外部记录。当匹配键没有匹配时，记录显示为该列为空。
The way you full outer join, Table0.num would need to exist in ALL the other tables. I.e. if num == 1 was only in Table0 and Table1, but not Table2 and 3, then it will not match on all 4 and thus be a null num.
完全外连接的方式，Table0.num 需要存在于所有其他表中。即，如果 num == 1 仅在 Table0 和 Table1 中，而不在 Table2 和 3 中，则它不会在所有 4 中匹配，因此为空 num。
What you probably want is something more like
你可能想要的是更像
SELECT Table0.num,
(Select SUM(Table1.qty1 ) From Table1 Where Table1.num = Table0.num) as one,
(Select SUM(Table2.qty1 ) From Table2 Where Table2.num = Table0.num) as two,
...
From Table0
My syntax might be a little off and there's probably more efficient ways. But the general idea is you do a subquery for each relation since they are independent.
我的语法可能有点不对，可能还有更有效的方法。但一般的想法是你为每个关系做一个子查询，因为它们是独立的。
回答by Alex Tam
There is a work-around solution. You can add one additional tableto join your tables. For example:
有一个变通的解决方案。您可以添加一张额外的表来连接您的表。例如：
Tablex; columns: tablex_id (PK, AI)(newly created table)
Tablex; 列：（tablex_id (PK, AI)新创建的表）
Table0; columns: tablex_id, num, desc(added new field 'tablex_id')
Table0; 列：（tablex_id, num, desc添加了新字段“tablex_id”）
Table1; columns: tablex_id, num, qty1(added new field 'tablex_id')
Table1; 列：（tablex_id, num, qty1添加了新字段“tablex_id”）
Table2; columns: tablex_id, num, qty2(added new field 'tablex_id')
Table2; 列：（tablex_id, num, qty2添加了新字段“tablex_id”）
Table3; columns: tablex_id, num, qty3(added new field 'tablex_id')
Table3; 列：（tablex_id, num, qty3添加了新字段“tablex_id”）
Table4; columns: tablex_id, num, qty4(added new field 'tablex_id')
Table4; 列：tablex_id, num, qty4（增加了新的领域“tablex_id”）
Every time you add record to Table0, Table1, Table2, Table3and Table4, you should add record to Tablexfirst and get the tablex_idand put it in your table(s) accordingly.
每次向Table0、Table1、Table2、Table3和 中Table4添加记录时，都应Tablex先向、、和 中添加记录，然后相应地获取tablex_id并放入表中。
If you want to join them, just do something like this:
如果您想加入他们，只需执行以下操作：
select t0.desc, t1.qty, t2.qty, t3.qty, t4.qty
from Tablex tx
left join Table0 t0
on tx.tablex_id = t0.tablex_id
left join Table0 t1
on tx.tablex_id = t1.tablex_id
left join Table2 t2
on tx.tablex_id = t2.tablex_id
left join Table3 t3
on tx.tablex_id = t3.tablex_id
left join Table4 t4
on tx.tablex_id = t4.tablex_id
