涉及SUM，LEFT JOIN和GROUP BY的复制 [英] Duplication involving SUM, LEFT JOIN and GROUP BY

问题描述

我遇到一个涉及SUM，LEFT OUTER JOIN和GROUP BY命令的问题，但无法弄清楚我的错误在哪里.

I have a problem involving a SUM, LEFT OUTER JOIN and GROUP BY commands, but can't figure out where my error is.

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

I have two tables, one for customer transactions and one for customer claims. A customer can have multiple transactions and multiple claims, but in both tables the rows are unique. Customers can also have no claims.

示例交易表:

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

理想的输出是将两个金额相加，并为客户"和年份"的每个唯一组合生成一行.

The desired output is to sum the two amounts, and produce a row for each unique combination of Customer and Year.

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值.但是我得到的是交易金额"值的重复，并且倍数与索赔"表中匹配的行数有关.

I have used a LEFT OUTER JOIN command with a GROUP BY command for the Customer and Year values. But what I am getting is a duplication of the Transaction Amount value, and the multiple relates to the number of matching rows in the Claims table.

因此，使用我的示例数据，我得到以下信息:

So using my example data I get the following:

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

在2007年，有两笔索赔导致Transactionss.Amount值乘以2(当有三笔索赔时，Transaction.Amount是三倍的，依此类推).

In the year 2007, there are two claims which has resulted in the Transactions.Amount value being multiplied by two (when there are three claims, the Transaction.Amount is tripled, etc).

我的代码如下:

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

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

Does the answer lie in subqueries? I am not familiar with them, so any pointers would be great. Thanks.

推荐答案

因此，了解发生了什么事情的第一步是删除SUM，仅选择交易金额和索赔金额.这样，您可以看到正在返回什么数据.您会看到，由于A/2007上的联接将每一行联接到Claims表中，因此其交易金额将是两次.

So the first step to see what is happening is to remove the SUMs and just select the transaction amount and claim amount. That way you can see what data is being returned. You'll see that the join on A/2007 will have the transaction amount twice, since it's joining each row to the claims table.

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

One solution is to use subquerys, like you said, to do the SUMs separately prior to joining.

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

根据您的限制，另一个可能的解决方案:

Another possible solution given your restrictions:

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小提琴

Third possible solution!! This one does not require any subqueries! See this 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

这篇关于涉及SUM，LEFT JOIN和GROUP BY的复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！