SQL 从两个表中获取几列的总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1388216/
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
Getting the sum of several columns from two tables
提问by Romain Linsolas
I want to get the sum of several columns from 2 different tables (these tables share the same structure).
我想从 2 个不同的表中获取几列的总和（这些表共享相同的结构）。
If I only consider one table, I would write this kind of query:
如果我只考虑一张表，我会写这样的查询：
SELECT MONTH_REF, SUM(amount1), SUM(amount2)
FROM T_FOO
WHERE seller = XXX
GROUP BY MONTH_REF;
However, I would like to also work with the data from the table T_BAR, and then have a selectquery that return the following columns:
但是，我还想处理表 T_BAR 中的数据，然后有一个select返回以下列的查询：
- MONTH_REF
- SUM(T_FOO.amount1) + SUM(T_BAR.amount1)
- SUM(T_FOO.amount2) + SUM(T_BAR.amount2)
- MONTH_REF
- SUM(T_FOO.amount1) + SUM(T_BAR.amount1)
- SUM(T_FOO.amount2) + SUM(T_BAR.amount2)
everything grouped by the value of MONTH_REF.
按 的值分组的所有内容MONTH_REF。
Note that a record for a given MONTH_REFcan be found in one table but not in the other table.
In this case, I would like to get the sum of T_FOO.amount1 + 0(or 0 + T_BAR.amount1).
请注意，MONTH_REF可以在一个表中找到给定记录，但不能在另一个表中找到。在这种情况下，我想得到T_FOO.amount1 + 0（或0 + T_BAR.amount1）的总和。
How can I write my SQL query to get this information?
如何编写 SQL 查询来获取此信息？
For information, my database is Oracle 10g.
有关信息，我的数据库是 Oracle 10g。
回答by Kobi
You can union your tables before the group by (this is on Oracle, by the way):
您可以在分组之前联合您的表（顺便说一下，这是在 Oracle 上）：
SELECT t.month_ref, SUM(t.amount1), SUM(t.amount2)
FROM (SELECT month_ref, amount1, amount2
FROM T_FOO
WHERE seller = XXX
UNION ALL
SELECT month_ref, amount1, amount2
FROM T_BAR
WHERE seller = XXX
) t
GROUP BY t.month_ref
You may also union the tables with the seller field and filter by it later (in case you need more advanced logic):
您还可以将表与卖家字段联合起来，稍后按它进行过滤（以防您需要更高级的逻辑）：
SELECT t.month_ref, SUM(t.amount1), SUM(t.amount2)
FROM (SELECT month_ref, amount1, amount2, seller
FROM T_FOO
UNION ALL
SELECT month_ref, amount1, amount2, seller
FROM T_BAR) t
where t.seller = XXX
GROUP BY t.month_ref
回答by Lieven Keersmaekers
Have you tried using a union?
您是否尝试过使用联合？
SELECT MONTH_REF, SUM(amount1), SUM(amount2)
FROM (
SELECT MONTH_REF, SUM(amount1) AS amount1, SUM(amount2) as amount2
FROM T_FOO
WHERE seller = XXX
GROUP BY MONTH_REF
UNION ALL SELECT MONTH_REF, SUM(amount1), SUM(amount2)
FROM T_BAR
WHERE seller = XXX
GROUP BY MONTH_REF
) tmp
GROUP BY MONTH_REF
回答by Ruffles
Alternatively, an outer join should also work:
或者，外连接也应该起作用：
SELECT month_ref,
SUM(t_foo.amount1) + SUM(t_bar.amount1),
SUM(t_foo.amount2)+SUM(t_bar.amount2)
FROM t_foo FULL OUTER JOIN t_bar
ON t_foo.month_ref = t_bar.month_ref
GROUP BY month_ref
回答by Romain Linsolas
I finally get this working using the Lieven's answer.
我终于使用Lieven的回答来解决这个问题。
Here is the correct code (amount1 = ...is not working on my environment, and there are too many ;in the query):
这是正确的代码（amount1 = ...不适用于我的环境，并且;查询中的代码太多）：
SELECT MONTH_REF, SUM(sumAmount1), SUM(sumAmount2)
FROM (
SELECT MONTH_REF, SUM(amount1) as sumAmount1, SUM(amount2) as sumAmount1
FROM T_FOO
WHERE seller = XXX
GROUP BY MONTH_REF
UNION ALL SELECT MONTH_REF, SUM(amount1), SUM(amount2)
FROM T_BAR
WHERE seller = XXX
GROUP BY MONTH_REF
) tmp
GROUP BY MONTH_REF
回答by sagar roy
SELECT (SELECT SUM(amount) from debit) as debitamounts, (SELECT SUM(amount) from credit) as creditamounts
