如何解决将表达式转换为数据类型int算术溢出错误?
技术问答
211 人阅读
|
0 人回复
|
2023-09-14
|
我有以下查询:
; K( {( Y* f% Q9 l) B( r \& ^-- 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.VersionFROM #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.MaxStartTimeGROUP BY mc.VersionORDER BY Version DESC,AVG(mc.JaguarStartupTime) ASCEligible临时表的定义是create table #Eligible Version nvarchar(50),JaguarStartupTime int, ServerName nvarchar(50),UpdatedOn datetime )无论我注释掉什么条件或聚合,总是会出现以下错误:Arithmetic overflow error converting expression todata type int。
x2 W5 E9 Q4 Y6 j+ b) Q我能从这里去哪里?如何进一步调试?
! Y9 `1 g5 g5 a5 s, G$ h2 ?编辑:样本数据. D+ D+ \5 G" A, r: g- n. Y
Version JaguarStartupTime ServerName UpdatedOn6.4.6. NewOrleansLA 2012-08-08 12:34:12.3306.5.1. BatonRougeLA 2012-08-08 18:33:17.4406.5.1. HonoluluHI 2012-08-09 04:42:50.4533 6 K0 ~) z+ b" x
解决方案: ( o) b6 O" ~# L) E; m' `2 L9 a9 v
你试过将军吗?jaguarstartup时间被迫转换为avg聚合中的bigint,如下所示…
, t3 x1 z, H' o1 MAVG(CAST(mc.JaguarStartupTime AS BIGINT))
# Z5 q' H8 y9 R应解决算术溢出问题。7 e+ u" c, g! z
要计算平均值,服务器首先需要能够对所有平均值进行计算int因此,要求平均值的数据类型必须能够存储这些值的总和,即使返回的答案是int的范围内 |
|
|
|
|
|