mysql-涉及SUM,LEFT JOIN和GROUP BY的复制

我有两个表,一个用于客户交易,另一个用于客户索赔.一个客户可以有多个交易和多个索赔,但是在两个表中,行都是唯一的.客户也可以没有索赔.

交易表示例：

Transactions:
Customer | Transaction Year | Amount
-------------------------------------
A        | 2007             | 100
A        | 2008             | 80
A        | 2008             | 50
A        | 2009             | 210

索赔表示例：

Claims:
Customer | Claim Year | Amount
-------------------------------
A        | 2007       | 30
A        | 2007       | 40
A        | 2009       | 110

期望的输出是将两个金额相加,并为“客户”和“年份”的每个唯一组合生成一行.

Desired Output:
Customer | Year | Transaction Amount | Claim Amount
----------------------------------------------------
A        | 2007 | 100                | 70
A        | 2008 | 130                | NULL
A        | 2009 | 210                | 110

我已将LEFT OUTER JOIN命令与GROUP BY命令一起用于Customer和Year值.但是我得到的是“交易金额”值的重复,并且倍数与“索赔”表中匹配的行数有关.

因此,使用示例数据,我得到以下信息：

Actual Output:
Customer | Year | Transaction Amount | Claim Amount
----------------------------------------------------
A        | 2007 | 200                | 70
A        | 2008 | 130                | NULL
A        | 2009 | 210                | 110

在2007年,有两笔索赔导致Transactions.Amount值乘以2(当有三笔索赔时,Transaction.Amount变成三倍等等).

我的代码如下：

SELECT Transactions.Customer,
   Transactions.Year,
   sum(Transactions.Transaction Amount),
   sum(Claims.Claim Amount)
FROM Transactions
   LEFT JOIN Claims ON Claims.Customer = Transactions.Customer
      AND Transactions.Year = Claims.Year
GROUP BY Transactions.Customer, Transactions.Year

答案是否在子查询中？我对它们不熟悉,所以任何指针都很好.谢谢.

一种解决方案是像您所说的那样,使用子查询在加入之前分别进行SUM.

SELECT 
   Transactions.Customer,
   Transactions.Year,
   SumTransaction,
   SumClaim
FROM (
      select Customer, Year, sum(Transaction Amount) SumTransaction 
      from Transactions
      group by Customer, Year
   ) Transactions
   LEFT JOIN (
      select Customer, Year, sum(Claim Amount) sumClaim 
      from Claims
      group by Customer, Year
   ) Claims
   ON Claims.Customer = Transactions.Customer
      AND Transactions.Year = Claims.Year

根据您的限制,另一个可能的解决方案：

SELECT 
   Transactions.Customer,
   Transactions.Year,
   SUM(Transaction Amount),
   (SELECT SUM(Claim Amount) from Claims where Claims.Customer = Transactions.Customer and Claims.Year = Transactions.Year)
FROM 
   Transactions
GROUP BY
   Customer, Year

第三种可能的解决方案！这个不需要任何子查询！看到这个SQL Fiddle

select
    t.Customer,
    t.Year,
    sum(distinct t.Amount),
    sum(c.Amount)
from
    Transactions t
    left join Claims c
        on  t.Customer = c.Customer
            and t.Year = c.year
group by
    t.Customer,
    t.Year