SQL Server 和 REAL 数据类型的舍入问题 [英] Rounding issue with SQL Server and REAL datatype

问题描述

在 SQL Server 2008 中四舍五入时，我看到一些奇怪的行为.给定以下代码:

DECLARE @Value REAL选择 @Value = .35选择回合(@Value，1)

我希望值是 0.4，但它输出的是 0.3.我必须假设这是因为存储的值实际上小于 0.35，类似于 .34999999999999.是这种情况，还是我做错了什么?有没有办法确保这符合预期，至少从可见值来看?

解决方案

当您使用像 REAL 和 FLOAT(相同的东西)这样的浮点值时，SQL Server ROUND() 函数遵循 IEEE 标准 754 并使用round向上"算法.

但这对于不同的浮点值意味着不同的东西.一些.5"值最终被存储为.49999999999"的近似值，其他值被存储为.500000001"等.它对实际存储的值进行四舍五入，而不是您开始时给它的值.

http://msdn.microsoft.com/en-us/library/ms187912.aspx

如果精确的十进制数学对您很重要，请使用 DECIMAL，而不是 FLOAT 或 REAL.

I'm seeing some strange behavior when rounding in SQL Server 2008. Given the following code:

DECLARE @Value REAL
SELECT @Value = .35
SELECT ROUND(@Value, 1)

I would expect the value to be .4, however it outputs .3. I must assume this is because the value stored is actually less than .35, something like .34999999999999. Is this the case, or am I doing something wrong? Is there a way to ensure this behaves as expected, at least from the visible value?

解决方案

When you are using floating-point values like REAL and FLOAT (same thing), the SQL Server ROUND() function follows IEEE Standard 754 and uses the "round up" algorithm.

But that means different things for different floating-point values. Some ".5" values end up getting stored as an approximation of ".49999999999", others as ".500000001", etc. It rounds up the value that is actually stored, not the value you gave it to begin with.

http://msdn.microsoft.com/en-us/library/ms187912.aspx

If exact decimal math matters to you, use DECIMAL, not FLOAT or REAL.

这篇关于SQL Server 和 REAL 数据类型的舍入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！