更新时间:2023-11-18 20:03:16
您需要执行以下两项操作之一(并且这两项都假定Table0
具有num
的所有实例)-
You need to do one of two things (and both of these assume that Table0
has all instances of num
) -
如果'leaf'表(1-4)的所有行均已累加,则简单的LEFT JOIN
(选择中有COALESCE()
)就足够了-您甚至不需要GROUP BY
.
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示例)