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

问题描述

我正在使用 SQL Server 2008 R2，并且我有一个 INT 列，其中插入的数据永远不会超过最大 INT，但我有使用 SUM 函数的查询，执行时超过最大 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 更改为 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屋！