SQL 多个表上的多个 FULL OUTER JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16167902/
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
Multiple FULL OUTER JOIN on multiple tables
提问by brykneval
I have multiple outer joins
我有多个外连接
SELECT A.column2
, B.column2
, C.column2
FROM
(
(SELECT month, column2 FROM table1) A
FULL OUTER JOIN
(SELECT month, column2 FROM table2) B on A.month= B.month
FULL OUTER JOIN
(SELECT month, column2 FROM table3) C on A.month= C.month
)
Now the last join is having a problem, its repeating when month of A is more than B but if B has more month that A we have OUTER JOIN in C with month of A which now repeats, so I guess having a FULL OUTER JOIN within two table might solve the problem?? Any indepth links??
现在最后一个连接有问题，当 A 的月份比 B 多时它会重复，但是如果 B 的月份比 A 多，我们在 C 中有 OUTER JOIN，而 A 的月份现在重复，所以我想在里面有一个 FULL OUTER JOIN两张表也许能解决问题？？有深度链接吗？？
Sample Data(Incorrect)
样本数据（不正确）
╔════════════╦═════════╦═════════════╗
║ Revenue ║ Budget ║ ActualMonth ║
╠════════════╬═════════╬═════════════╣
║ 6.9172 ║ 3.5046 ║ Jan ║
║ 7.3273 ║ 3.7383 ║ Feb ║
║ 7.3273 ║ 3.9719 ║ Mar ║
║ 7.2726 ║ 4.2056 ║ Apr ║
║ 7.2595 ║ 6.7757 ║ May ║
║ 7.2726 ║ 6.7757 ║ Jun ║
║ 0.41 ║ 0.00 ║ Jul ║
║ 0.41 ║ 0.00 ║ Aug ║
║ 0.41 ║ 0.00 ║ Sep ║
║ 0.41 ║ 0.00 ║ Oct ║
║ 7.4696 ║ 0.00 ║ Nov ║
║ 7.4696 ║ 0.00 ║ Dec ║
║ 0.00 ║ 9.3457 ║ Sep ║
║ 0.00 ║ 16.3551 ║ Dec ║
║ 0.00 ║ 6.3084 ║ Jul ║
║ 0.00 ║ 14.0186 ║ Oct ║
║ 0.00 ║ 16.3551 ║ Nov ║
║ 0.00 ║ 6.1915 ║ Aug ║
╚════════════╩═════════╩═════════════╝
Correct Data
正确的数据
╔════════════╦═════════╦═════════════╗
║ Revenue ║ Budget ║ ActualMonth ║
╠════════════╬═════════╬═════════════╣
║ 6.9172 ║ 3.5046 ║ Jan ║
║ 7.3273 ║ 3.7383 ║ Feb ║
║ 7.3273 ║ 3.9719 ║ Mar ║
║ 7.2726 ║ 4.2056 ║ Apr ║
║ 7.2595 ║ 6.7757 ║ May ║
║ 7.2726 ║ 6.7757 ║ Jun ║
║ 0.41 ║ 6.3084 ║ Jul ║
║ 0.41 ║ 6.1915 ║ Aug ║
║ 0.41 ║ 9.3457 ║ Sep ║
║ 0.41 ║ 14.0186 ║ Oct ║
║ 7.4696 ║ 16.3551 ║ Nov ║
║ 7.4696 ║ 16.3551 ║ Dec ║
╚════════════╩═════════╩═════════════╝
回答by Serge
SELECT A.column2
, B.column2
, C.column2
FROM
(
(SELECT month, column2 FROM table1) A
FULL OUTER JOIN
(SELECT month, column2 FROM table2) B on A.month= B.month
FULL OUTER JOIN
(SELECT month, column2 FROM table3) C on ISNULL(A.month, B.month) = C.month
)
回答by Roman Pekar
One of the ways to do this could be create "anchor" table from all possible data from all three tables and then use left outer join:
一种方法是从所有三个表中的所有可能数据创建“锚”表，然后使用left outer join：
select
A.column2,
B.column2,
C.column2
from (
select distinct month from table1
union
select distinct month from table2
union
select distinct month from table3
) as X
left outer join table1 as A on A.month = X.month
left outer join table2 as B on B.month = X.month
left outer join table3 as C on C.month = X.month
回答by Tab Alleman
I can think of 2 ways off the bat that you can address this, depending on what the actual logic is to define the results you want.
我可以立即想到两种方法来解决这个问题，具体取决于定义所需结果的实际逻辑。
The first, and most fool-proof way, is to use GROUP BY month, and use aggregate functions like MAX(column2) to get the non-zero rows only, or if there are multiple non-zero rows you want to add, use SUM(). This is the best solution if there is an aggregate function that fulfills your logical intent.
第一种也是最简单的方法是使用 GROUP BY 月份，并使用 MAX(column2) 等聚合函数仅获取非零行，或者如果要添加多个非零行，请使用和（）。如果存在满足您的逻辑意图的聚合函数，则这是最佳解决方案。
Another is to include more conditions in your JOIN, like "WHERE a.month=b.month AND b.column2 > 0", but that still won't solve the problem if there can be more than one non-zero row.
另一种方法是在您的 JOIN 中包含更多条件，例如“WHERE a.month=b.month AND b.column2 > 0”，但如果可以有多个非零行，这仍然无法解决问题。
回答by Aleksandr Fedorenko
Use option with COALESCEfunction to determine a column grouping.
使用选项和COALESCE函数来确定列分组。
SELECT COALESCE(t1.Month, t2.Month, t3.Month) AS Month,
SUM(ISNULL(t1.Col1, 0)) AS t1Col1,
SUM(ISNULL(t2.Col1, 0)) AS t2Col1,
SUM(ISNULL(t3.Col1, 0)) AS t3Col1
FROM dbo.table1 t1 FULL OUTER JOIN dbo.table2 t2 ON t1.Month = t2.Month
FULL OUTER JOIN dbo.table3 t3 ON t1.Month = t3.Month
GROUP BY COALESCE(t1.Month, t2.Month, t3.Month)
回答by Ian Kenney
something like
就像是
select month, sum(a) a, sum(b) b, sum(c) c from (
SELECT month, column2 A, 0 B, 0 C FROM table1
union
SELECT month, 0 A, column2 B, 0 C FROM table2
union
SELECT month, 0 A, 0 B, column2 C FROM table3
) x
group by month
