如何解决将表达式转换为数据类型 int 的算术溢出错误? [英] How get around the arithmetic overflow error converting expression to data type int?

问题描述

我有以下查询:

-- CTE to remove outliers, e.g. remove the fastest and slowest results
;WITH MinMaxCTE AS 
(
  SELECT ServerName, CONVERT(VARCHAR(10), UpdatedOn, 101) AS [Date], Version, 
    MIN(JaguarStartupTime) AS MinStartTime, MAX(JaguarStartupTime) AS MaxStartTime
  FROM dbo.MachineConfiguration (NOLOCK)
  WHERE DomainLogin NOT IN (SELECT DomainLogin FROM dbo.SupportGroup)
  GROUP BY ServerName, CONVERT(VARCHAR(10), UpdatedOn, 101), Version
)

SELECT  AVG(mc.JaguarStartupTime) AS AverageTime
    , COUNT(*) AS NumEntries
    , mc.Version
FROM    #Eligible mc (NOLOCK)
JOIN MinMaxCTE cte ON mc.ServerName = cte.ServerName 
  AND CONVERT(VARCHAR(10), mc.UpdatedOn, 101) = cte.[Date] 
  AND mc.Version = cte.Version 
  AND mc.JaguarStartupTime <> cte.MinStartTime 
  AND mc.JaguarStartupTime <> cte.MaxStartTime
GROUP BY mc.Version
ORDER BY Version DESC, AVG(mc.JaguarStartupTime) ASC

#Eligible 临时表的定义是

The definition of the #Eligible temp table is

create table #Eligible (
    Version nvarchar(50), JaguarStartupTime int, 
    ServerName nvarchar(50), UpdatedOn datetime )

无论我注释掉什么条件或聚合，总是出现以下错误:Arithmetic overflow error conversion to data type int.

No matter what condition or aggregation I comment out, I always get the following error: Arithmetic overflow error converting expression to data type int.

我可以从这里去哪里?我该如何进一步调试?

Where can I go from here? How do I debug this further?

示例数据

Version JaguarStartupTime    ServerName     UpdatedOn
6.4.6.082      16040         NewOrleansLA   2012-08-08 12:34:12.330
6.5.1.012      40390         BatonRougeLA   2012-08-08 18:33:17.440
6.5.1.012      48379         HonoluluHI     2012-08-09 04:42:50.453

推荐答案

您是否尝试过将 jaguar 启动时间转换为 avg 聚合中的 bigint 像这样...

Have you tried casting your jaguarstartup times to a bigint in your avg aggregate like so...

AVG(CAST(mc.JaguarStartupTime AS BIGINT))

AVG(CAST(mc.JaguarStartupTime AS BIGINT))

这应该可以解决算术溢出问题.

This should sort out the arithmetic overflow.

要计算平均平均值，服务器需要首先对所有整数求和，因此您要求平均值的数据类型需要能够存储这些值的总和，即使返回的答案在范围内一个整数

To calculate the mean average the server needs to be able to sum all of the ints first, so the datatype you are averaging on needs to able to store the sum of those values, even if the returned answer is within the range of of an int

这篇关于如何解决将表达式转换为数据类型 int 的算术溢出错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！