我遇到一个涉及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命令一起用于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,仅选择交易金额和索赔金额.这样,您可以看到正在返回什么数据.您会看到,由于A / 2007上的联接将每一行联接到Claims表,所以事务处理量将是两次.
一种解决方案是像您所说的那样,使用子查询在加入之前分别进行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
标签:sql,mysql
来源: https://codeday.me/bug/20191122/2062021.html