将 SQL FLOAT 转换为 SQL INT，丢失数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4398700/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting SQL FLOAT to SQL INT, lost data
提问by Anish Patel
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 = 15662However when coming from the @datatable, 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.
有谁知道这是为什么？我猜这是某种浮动细微差别。但我从没想过它会遇到像上面这样的问题。有任何想法吗？在此先感谢您的帮助。
回答by Alin Purcaru
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)来代替。
回答by Lex
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).
由于计算机上浮点数的特殊性，这是浮点数数据类型的常见行为。使用小数点后固定位数的decimal（数字）数据类型。例如，十进制(10, 6)。
