将SQL FLOAT转换为SQL INT，丢失数据

I'm having some problems with converting some data stored in a FLOAT datatype to data stored in an INT datatype. The below example illustrates my problem:

将存储在FLOAT数据类型中的某些数据转换为存储在INT数据类型中的数据时遇到一些问题。以下示例说明了我的问题:

DECLARE @data TABLE
(
 id INT,
 weight FLOAT
)
INSERT INTO @data VALUES(1,0.015662)

SELECT CAST(weight * 1000000 AS INT) FROM @data
SELECT 0.015662 * 1000000
SELECT CAST(0.015662 * 1000000 AS INT)

The desired results would be: ID = 1 VALUE = 15662 However when coming from the @data table, I don't seem to get this. I instead get ID = 1 VALUE = 15661.

期望的结果将是:ID = 1 VALUE = 15662但是当来自@data表时,我似乎没有得到这个。我改为ID = 1 VALUE = 15661。

Does anyone have any idea why this is? I'm guessing it's some sort of float nuance. But I never thought it would have a problem with something like the above. Any ideas? Thanks in advance for your help.

有谁知道为什么会这样?我猜它是某种漂浮的细微差别。但我从未想过会出现上述问题。有任何想法吗?在此先感谢您的帮助。

2 个解决方案

#1

This is the classic (int)((0.1+0.7)*10) problem. Because floats have arbitrary precision some data loss when casting to int is possible even for very simple cases.

这是经典(int)((0.1 + 0.7)* 10)问题。因为浮点数具有任意精度,所以即使对于非常简单的情况,也可以在转换为int时丢失一些数据。

Use ROUND(weight * 1000000.0, 0) instead.

请改用ROUND(weight * 1000000.0,0)。

#2

This is common behaviour for float data type due to specificity of float on computers. Use decimal (number) data type with fixed digits after decimal point. For example, decimal(10, 6).

由于浮点数在计算机上的特殊性,这是浮点数据类型的常见行为。使用小数点后固定位的十进制(数字)数据类型。例如,十进制(10,6)。

#1