在多个表上有多个FULL OUTER JOIN [英] Multiple FULL OUTER JOIN on multiple tables
本文介绍了在多个表上有多个FULL OUTER JOIN的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有多个外部联接
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
)
现在最后一个联接有问题,当A的月份大于B时,它会重复,但是如果B的月份比A多,则我们在C中有OUTER JOIN,而A的月份现在又重复了,所以我猜有一个FULL两个表内的OUTER JOIN可能会解决问题??有任何深入的链接吗?
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??
样本数据(不正确)
╔════════════╦═════════╦═════════════╗
║ 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 ║
╚════════════╩═════════╩═════════════╝
更正数据
╔════════════╦═════════╦═════════════╗
║ 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 ║
╚════════════╩═════════╩═════════════╝
推荐答案
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
)
这篇关于在多个表上有多个FULL OUTER JOIN的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文