涉及求和、左联接和分组方式的复制

Question

我遇到了一个涉及SUM、LEFT OUTER 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命令。但我得到的是交易金额值的一个副本，该倍数与索赔表中匹配行的数量有关。

因此，使用我的示例html" target="_blank">数据，我得到以下结果：

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

在2007年，有两项索赔导致了交易。金额值乘以2（当有三项索赔时，交易。金额增加三倍，以此类推）。

我的代码如下：

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

答案是否在子查询中？我不熟悉他们，所以任何指针都会很棒。谢谢。

Answer 1

With T as (
    SELECT  Customer,
            [Transaction Year],
            sum(Amount) AS TransactionAmount
    FROM Transactions
    GROUP BY Customer, [Transaction Year]
), C AS 
    SELECT  Customer,
            [Claim Year],
            sum(Amount) as ClaimAmount
    FROM Claims
    GROUP BY Customer, [Claim Year]
)
SELECT  T.Customer,
        [Transactions Year],
        TransactionAmount,
        ClaimAmount
FROM    T
   LEFT JOIN C ON C.Customer = T.Customer
      AND [Transactions Year] = [Claim Year]

Answer 2

由于您有两个索赔，查询将对2007年的交易金额进行两次计数，因此交易金额将被计数两次。

i、 e.正在使用的返回数据是：

Customer | Transaction Year | Transaction Amount | Claim Amount
----------------------------------------------------------------
A        | 2007             | 100                | 30
A        | 2007             | 100                | 40
A        | 2008             | 80                 |
A        | 2008             | 50                 |
A        | 2009             | 210                | 110

像下面这样的东西，虽然不漂亮，但应该可以解决这个问题：

SELECT 
   t.Customer
   ,t.Year
   ,[Transaction Amount] = SUM(t.[Transaction Amount])
   ,[Claim Amount] = c.[Claim Amount]
FROM 
    Transactions t
    LEFT JOIN (
        SELECT 
            Customer
            ,Year
            ,SUM([Claim Amount])
        FROM
           Claims
        GROUP BY
           Customer, Year
    ) c ON c.Customer = t.Customer c.Year = t.Year
GROUP BY t.Customer, t.Year, c.[Claim Amount]

Answer 3

因此，了解发生了什么的第一步是删除SUM，只需选择交易金额和索赔金额。这样，您可以看到返回的数据。您将看到A/2007上的连接将有两次事务金额，因为它将每行连接到声明表。

一种解决方案是使用子查询，如您所说，在加入之前分别进行求和。

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

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