在INT列上使用SUM时如何防止算术溢出错误？ [英] How to prevent arithmetic overflow error when using SUM on INT column?

问题描述

我正在使用 SQL Server 2008 R2 ，我有一个 INT 列，其中插入的数据永远不会超过最大 INT ，但是我有一个使用 SUM 函数的查询，该函数在执行时超过max INT 限制并抛出标题中提到的错误。

I am using SQL Server 2008 R2 and I have an INT column where the data inserted never surpasses the max INT, but I have a query which uses the SUM function which when executed surpasses the max INT limit and throws the error mentioned in the title.

我希望能够执行此查询而不更改 INT <的列类型/ code>到 BIGINT 。

I want to be able to execute this query without changing the column type from INT to BIGINT.

这是我的查询：

SELECT    UserId,
          SUM( PokemonExp )     AS TotalExp,
          MAX( PokemonLevel )   AS MaxPokeLevel

FROM      mytable

GROUP BY  UserId
ORDER BY  TotalExp DESC

注意： PokemonExp 列的类型为 INT 。

Note: The PokemonExp column is of type INT.

推荐答案

SUM 中的表达类型确定退货类型。

Type of expression in SUM determines return type.

尝试以下方法：

SELECT    UserId,
          SUM( CAST( PokemonExp AS BIGINT ))  AS TotalExp,
          MAX( PokemonLevel )                 AS MaxPokeLevel

FROM      mytable

GROUP BY  UserId
ORDER BY  TotalExp DESC

这篇关于在INT列上使用SUM时如何防止算术溢出错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！