如何对 MySQL 中多个表的列求和？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7432178/
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
How can I sum columns across multiple tables in MySQL?
提问by phpJs
In MySQL I have two tables:
在 MySQL 中，我有两个表：
Table MC:
----------------
|TransNo | Qty |
|--------|-----|
| xxx1 | 4 |
| xxx3 | 3 |
and
和
Table Amex:
----------------
|TransNo | Qty |
|---------|-----|
| xxx1 | 2 |
| xxx5 | 1 |
I need to sum the Qtycolumn from table MC(eq. 7) and table Amex(eq. 3) and have result as Total Qty.
我需要对Qty表MC（eq. 7）和表Amex（eq. 3）中的列求和，并将结果作为总数量。
When I do
当我做
SELECT (SUM(amex.Qty) + SUM(mc.Qty)) as total_qty from amex, mc
I get the cartesian product (20), but the correct answer I need is 10. How do I need to change this query to get the correct result?
我得到笛卡尔积 (20)，但我需要的正确答案是 10。我需要如何更改此查询才能获得正确结果？
回答by Joe Stefanelli
SELECT SUM(t.Qty) AS total_qty
FROM (SELECT Qty FROM MC
UNION ALL
SELECT Qty FROM Amex) t
回答by Torin Pena
If you wish to avoid using Union or Union ALL (probably for efficiency reasons), then the following works:
如果您希望避免使用 Union 或 Union ALL（可能是出于效率原因），则以下方法有效：
SELECT (1.Qty+2.Qty) AS total_qty FROM (SELECT SUM(Qty) Qty FROM MC) 1,
(SELECT SUM(Qty) Qty FROM Amex) 2;
Here's an example for if you wish to expand this out to include a Group By condition. Let's say we have a Cust_ID on both MC and Amex to identify the customer which made each order, and we want to know the sums for each customer. The code would then look like this:
这是一个示例，用于说明您是否希望将其扩展为包含 Group By 条件。假设我们在 MC 和 Amex 上都有一个 Cust_ID 来识别每个订单的客户，我们想知道每个客户的总和。代码如下所示：
SELECT COALESCE(1.Cust_ID, 2.Cust_ID) Cust_ID, (1.Qty+2.Qty) AS total_qty
FROM (SELECT Cust_ID, SUM(Qty) Qty FROM MC GROUP BY Cust_ID) 1
FULL OUTER JOIN (SELECT Cust_ID, SUM(Qty) Qty FROM Amex GROUP BY Cust_ID) 2 ON 1.Cust_ID = 2.Cust_ID;
If a Customer table exists in the database, then this can be simplified to:
如果数据库中存在 Customer 表，则可以简化为：
SELECT c.Cust_ID, (1.Qty+2.Qty) AS total_qty FROM Customer c
LEFT JOIN (SELECT Cust_ID, SUM(Qty) Qty FROM MC GROUP BY Cust_ID) 1 ON 1.Cust_ID = c.Cust_ID
LEFT JOIN (SELECT Cust_ID, SUM(Qty) Qty FROM Amex GROUP BY Cust_ID) 2 ON 2.Cust_ID = c.Cust_ID;
回答by PeTaX
And what about:
还有：
SELECT (SELECT SUM(`Qty`) FROM `MC`) + (SELECT SUM(`Qty`) FROM `Amex`) AS `sumf`;
回答by Dark Falcon
SELECT SUM(Qty) AS total_qty FROM (SELECT Qty FROM amex UNION SELECT Qty FROM mc);
