MySQL 查询两个不同表中两个字段的 SUM
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18148188/
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
Query SUM for two fields in two different tables
提问by Gordon Linoff
I am trying to determine how to do sum fields between two tables.
我正在尝试确定如何在两个表之间进行求和字段。
In table 1, we'll call it gegevens for short, I would have, for example, gegevenID, vertrekdatum, prijs
在表 1 中，我们将其简称为 gegevens，例如，gegevenID、vertrekdatum、prijs
In table 2, we'll call it fees, I would have , for example, feeID, gegevenID, amount
在表 2 中，我们将其称为费用，例如，feeID、gegevenID、金额
I want to take and sum the values for prijs based on year(vertrekdatum) from gegevens.
我想根据 gegevens 的年份（vertrekdatum）对 prijs 的值进行求和。
I had tried to do a LEFT JOIN and it worked until there were two records for the same gegevenID in the fee's table., then it doubled the prijs.
我曾尝试进行 LEFT JOIN，它一直有效，直到费用表中有两个相同 gegevenID 的记录。然后它使 prijs 翻了一番。
Table example:
表示例：
GEGEVENS
----------------------------------
gegevenID | vertrekdatum | prijs |
----------------------------------
| 1 | 2011-01-01 |1385.88|
| 2 | 2011-03-01 | 450.26|
| 3 | 2012-01-01 |2505.10|
----------------------------------
FEES
----------------------------
feeID | gegevenID | amount |
----------------------------
| 1 | 2 | 50.00|
| 2 | 2 | 126.00|
| 3 | 3 | 50.00|
----------------------------
The results that I wantare
我想要的结果是
TOTALS
--------------------------------------------
| year | SumOfPrijs | SumOfFees | Total |
--------------------------------------------
| 2011 | 1836.14 | 176.00 | 2012.14 |
| 2012 | 2505.10 | 50.00 | 2555.10 |
--------------------------------------------
This query resulted in the doubled 'prijs' when it took into account there were two rows in the fees table for one gegevenID.
当考虑到费用表中有两个行用于一个 gegevenID 时，该查询会导致“prijs”翻倍。
SELECT sum(prijs) as SumOfPrijs, sum(amount) as SumOfFees, sum(prijs)+sum(amount) AS
Total, year(vertrekdatum) as year
FROM tbl_vluchtgegevens vg
LEFT JOIN tbl_fees f
ON f.gegevenID = vg.gegevenID
WHERE vertrekdatum <=NOW()
GROUP by year(vertrekdatum)
Any thoughts would be great.
任何想法都会很棒。
回答by Gordon Linoff
You need to use a subquery to aggregate the fees table before the join:
您需要在加入之前使用子查询来聚合费用表：
SELECT sum(prijs) as SumOfPrijs, sum(amount) as SumOfFees, sum(prijs)+sum(amount) AS
Total, year(vertrekdatum) as year
FROM tbl_vluchtgegevens vg LEFT JOIN
(select f.gegevenId, sum(amount) as Amount
from tbl_fees f
group by f.gegevenId
) f
ON f.gegevenID = vg.gegevenID
WHERE vertrekdatum <=NOW()
GROUP by year(vertrekdatum);
The problem is that the multiple fees on on "gegeven" is causing the join to produce unexpected rows, that affect the sum.
问题是“gegeven”上的多重费用导致连接产生意外的行，从而影响总和。
回答by Ed Gibbs
Joining in any form will double the values when there are two gegevenIDrows in fees(or triple them if there are three, and so on).
当有两gegevenID行时，以任何形式加入都会使值加倍fees（如果有三行，则将值加倍，依此类推）。
The best workaround I can think of is to calculate the sums independently - one subquery for price and one subquery for fees - and then put the results together:
我能想到的最佳解决方法是独立计算总和 - 一个价格子查询和一个费用子查询 - 然后将结果放在一起：
SELECT
p.year,
p.SumOfPrijs,
f.SumOfFees,
p.SumOfPrijs + f.SumOfFees AS Total
FROM (
SELECT
YEAR(vertrekdatum) AS year,
SUM(prijs) AS SumOfPrijs
FROM gegevens
GROUP BY YEAR(vertrekdatum)
) p
LEFT JOIN (
SELECT
YEAR(vertrekdatum) as year,
SUM(amount) AS SumOfFees
FROM gegevens
INNER JOIN fees ON gegevens.gegevenID = fees.gegevenID
GROUP BY YEAR(vertrekdatum)
) f ON p.year = f.year
There's a SQL Fiddle here.
有一个SQL小提琴这里。
