具有多个表的SQL完整外部联接 [英] SQL Full Outer Join with Multiple Tables

问题描述

首先我有4张桌子

Table0, Columns: num, desc
Table1, Columns: num, qty1
Table2, Columns: num, qty2
Table3, Columns: num, qty3
Table4, Columns: num, qty4

(并非所有num都具有qty1或qty2或qty3或qty4的值，因此我需要完全连接) 和我的查询:

(not all num have values in qty1 or qty2 or qty3 or qty4, therefore I need a full join) and my query:

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

不知何故，它仅返回1行数据:

Somehow its returning just 1 row of data:

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

喜欢:

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 

推荐答案

您需要执行以下两项操作之一(并且这两项都假定Table0具有num的所有实例)-

You need to do one of two things (and both of these assume that Table0 has all instances of num) -

1. 如果'leaf'表(1-4)的所有行均已累加，则简单的LEFT JOIN(选择中有COALESCE())就足够了-您甚至不需要GROUP BY.

1. If all rows are already summed for the 'leaf' tables (1 - 4), then a simple LEFT JOIN (with a COALESCE() in the select) will suffice - you don't even need the GROUP BY.

如果您需要对行进行汇总，则需要在连接之前将它们汇总，因为否则否则不同表中的每行多行将导致结果为相乘.

If you need the rows summed, you're going to need to sum them before the join, given that otherwise multiple rows per num in different tables will cause the results to multiply.

类似这样的东西:

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

(正在运行 SQLFiddle示例)

这篇关于具有多个表的SQL完整外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！