SQL Server 2005 数值精度丢失问题

Question

SQL Server 2005 数值精度丢失问题

sql-serverfloating-pointtype-conversioncastingfloating-accuracy

5

调试一些与金融相关的 SQL 代码时，发现 numeric(24,8) 精度存在奇怪问题。

在 MSSQL 上运行以下查询，您会得到 A + B * C 表达式的结果是 0.123457：

SELECT A, B, C, A + B * C FROM ( SELECT CAST(0.12345678 AS NUMERIC(24,8)) AS A, CAST(0 AS NUMERIC(24,8)) AS B, CAST(500 AS NUMERIC(24,8)) AS C ) T

因此，我们失去了 2 个重要的数字。尝试以不同的方式解决这个问题后，我得出结论：将中间乘法结果（即零）转换为 numeric(24,8) 将正常工作。

最终我有了一个解决方案。但我仍有一个问题——MSSQL 为什么会以这种方式行事，并且我的样本中实际发生了哪些类型转换？

- Ilya Komakhin

3个回答

0

尽管精度、比例和长度（Transact-SQL）上所说的不同，但我认为它也将结果为乘法的 NUMERIC 类型的最小“比例”（小数位数）应用于6，就像对除法等操作一样。

- phoenix10k

0

根据eed3si9n所指出的逻辑和您在问题中提到的，似乎在进行数学运算时最好的方法是将它们提取到一个函数中，并在每个操作后指定精度。

在这种情况下，该函数可能如下所示：

create function dbo.myMath(@a as numeric(24,8), @b as numeric(24,8), @c as numeric(24,8))
returns  numeric(24,8)
as
begin 
    declare @d as numeric(24,8)
    set @d = @b* @c
    return @a + @d
end

- kristof

这种方法可能无法解决 SQL Server 截断小数部分的问题。为了保存小数部分，可能需要将 @a 和 @b 强制转换为 double 类型。 - Eugene Yokota

谢谢，当我在SQL中进行一些精确计算时，我会牢记这点。到目前为止，我还没有需要使用它，但知道可能存在一些需要考虑的问题是很好的。 - kristof

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- eed3si9n · Accepted Answer

正如浮点数的加法不准确一样，如果超出精度，十进制类型的乘法也可能不准确（或导致不准确）。请参见数据类型转换和decimal and numeric。

由于你将NUMERIC(24,8)和NUMERIC(24,8)相乘，而 SQL Server 只会检查类型而不是内容，所以当它无法保存所有48位精度（最大为38）时，它可能会尝试保存潜在的16个非十进制数字（24-8）。将两个数字组合起来，你得到32个非十进制数字，这只留下了6个小数位（38-32）。

因此，原始查询

SELECT A, B, C, A + B * C
FROM ( SELECT CAST(0.12345678 AS NUMERIC(24,8)) AS A,
  CAST(0 AS NUMERIC(24,8)) AS B,
  CAST(500 AS NUMERIC(24,8)) AS C ) T

简化为

SELECT A, B, C, A + D
FROM ( SELECT CAST(0.12345678 AS NUMERIC(24,8)) AS A,
  CAST(0 AS NUMERIC(24,8)) AS B,
  CAST(500 AS NUMERIC(24,8)) AS C,
  CAST(0 AS NUMERIC(38,6)) AS D ) T

再次说明，在NUMERIC(24,8)和NUMERIC(38,6)之间，SQL Server将尝试保存最多32位非小数位数字，因此A + D简化为

SELECT CAST(0.12345678 AS NUMERIC(38,6))

经过四舍五入，您将得到0.123457。