我有这个查询：
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
运行它时出现此错误：
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.
我可以理解，外部联接会导致此错误，因为当我删除最后一个总和时，它将运行正常。但是我记得做过这样的查询，并NULL在外部联接表上求和。
我能做些什么？
尝试这个
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
中的表达式类型SUM确定输出的类型。因此，当您的总和超过整数限制时，您将收到此错误。为了将您的数据视为整数而不出现错误，您需要cast将该amount列设置为BIGINT，因此总和将为type BIGINT。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句