如何在MySQL中的多个表中对列进行求和？

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 Qty column from table MC (eq. 7) and table Amex (eq. 3) and have result as Total Qty.

我需要将表MC(等式7)和表Amex(等式3)中的Qty列相加,并将结果作为总数量。

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.我如何更改此查询以获得正确的结果?

4 个解决方案

#1

SELECT SUM(t.Qty) AS total_qty
    FROM (SELECT Qty FROM MC
          UNION ALL
          SELECT Qty FROM Amex) t

#2

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;

#3

SELECT SUM(Qty) AS total_qty FROM (SELECT Qty FROM amex UNION SELECT Qty FROM mc);

#4

And what about:

那怎么样:

SELECT (SELECT SUM(`Qty`) FROM `MC`) + (SELECT SUM(`Qty`) FROM `Amex`) AS `sumf`;

#1

SELECT SUM(t.Qty) AS total_qty
    FROM (SELECT Qty FROM MC
          UNION ALL
          SELECT Qty FROM Amex) t

#2