SQL:左连接和和和:算术溢出错误将表达式转换为数据类型int

I have this query:

我有这个查询:

select p.UserName, sum(b.PercentRials) as amount, sum(r.Amount) as Pays
from bills b inner join UserProfiles p on b.PayerUserName=p.UserName
left outer join PayReceipts r on p.UserName=r.UserName
where p.[Percent]>0 and b.PayDate>'2014-11-20'
group by p.UserName

I get this error when running it:

我在运行时得到这个错误:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

I can understand that the outer join causes this error because when I remove the last sum, it runs OK. but I remember doing such queries and getting NULL for summation on outer join table.

我可以理解外部连接导致这个错误，因为当我删除最后一个和时，它运行良好。但我记得我做过这样的查询，并得到了外部连接表的和的NULL。

What can I do?

我能做什么?

2 个解决方案

#1

Try this

试试这个

select p.UserName, sum(b.PercentRials) as amount, sum(CAST (r.Amount AS BIGINT)) as Pays
from bills b inner join UserProfiles p on b.PayerUserName=p.UserName
left outer join PayReceipts r on p.UserName=r.UserName
where p.[Percent]>0 and b.PayDate>'2014-11-20'
group by p.UserName

Type of expression in SUM determines the type of the output. So when your sum exceeds the integer limit, you will receive this error. To perceive your data as integer without getting the error, you need to cast the amount column to be BIGINT and therefore the sum will be of type BIGINT.

SUM中的表达式类型决定了输出的类型。所以当你的和超过整数极限时，你会收到这个错误。要将数据感知为整数而不获取错误，需要将amount列转换为BIGINT，因此sum的类型为BIGINT。

#2

If you work at a financial firm,

如果你在金融公司工作，

sum(r.Amount) as Pays

might very well overflow the meagre integer limit of two billion. You could cast to a larger variable type, like a decimal:

可能很好地溢出了二十亿的极小的整数极限。可以转换成较大的变量类型，比如小数:

sum(cast(r.Amount as decimal(38,2))) as Pays

#1