如何在SQL服务器的值为2位小数的一列的总和导致0000000000087？ [英] How can a sum of a column in sql server where values are 2 decimal places result in 0000000000087?

问题描述

我有一个包含浮动列的表。

表中有许多记录。

当我做一个组时，它显示浮点列只有值：
0.1
0.2
0.25
0.5
1
2
3

但是，当我做一个SUM它的结果是：
58.350000000000087

0000000000087从哪里来？是因为我使用的是浮点型而不是十进制/数字吗？我读了一些关于这个论坛的帖子，但数学伤了我的头。什么是正确的方法来获得准确的结果？解决方案IEEE 754（和相关的）浮点数不能准确地表示许多十进制值。当你执行算术运算时，例如求和，有时候最终得到的值不能很好的表示出错。

请参阅这篇文章以获取更多信息： 计算机科学家应该知道什么是浮点运算

根据所使用的SQL服务器，有十进制值的数字类型是精确的，而不是十进制值的数字类型。例如，正如你所发现的那样， float 对小数不准确。但是， money 和 decimal 是。

I have a table that contains a float column.

There are many records in that table.

When I do a Group By it shows that the float column has only the values: 0.1 0.2 0.25 0.5 1 2 3

But when I do a SUM it results in: 58.350000000000087

Where does the 0000000000087 come from? Is it because I'm using a float type instead of a decimal/numeric? I read some forum posts about this but the math hurts my head. What would be the correct way to get accurate results?

解决方案

IEEE 754 (and related) floating point numbers cannot accurately represent many decimal values. When you perform arithmetic operations, such as summation, you sometimes end up with values that cannot be well represented, and the error occurs.

Please see this article for more info: What Every Computer Scientist Should Know About Floating-Point Arithmetic

Depending on the SQL server you are using, there are numeric types that are precise for decimal values, and those that aren't. For example, as you have found, float is not precise for decimals. However, money and decimal are.

这篇关于如何在SQL服务器的值为2位小数的一列的总和导致0000000000087？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！