为什么在访问查询中看到-0,000000000000001? [英] Why see -0,000000000000001 in access query?

问题描述

我有一个SQL:

SELECT Sum(Field1), Sum(Field2), Sum(Field1)+Sum(Field2)
FROM Table
GROUP BY DateField
HAVING Sum(Field1)+Sum(Field2)<>0;

问题有时是field1和field2的总和，例如:9.5-10.3，结果是-0,800000000000001.有人可以解释为什么会发生这种情况以及如何解决吗?

Problem is sometimes Sum of field1 and field2 is value like: 9.5-10.3 and the result is -0,800000000000001. Could anybody explain why this happens and how to solve it?

推荐答案

我确定这是因为float数据类型(MS Access中为Double或Single)不精确.它不像十进制那样简单，它是按10的幂进行缩放的简单值.如果我没有记错的话，float值可以有不同的分母，这意味着它们不一定总是完全转换回以10为底的数字.

I'm certain it is because the float data type (aka Double or Single in MS Access) is inexact. It is not like decimal which is a simple value scaled by a power of 10. If I'm remembering correctly, float values can have different denominators which means that they don't always convert back to base 10 exactly.

解决方法是将Field1和Field2从float/single/double/double更改为十进制或货币.如果您给出了需要存储的最小和最大值的示例，包括所需的最小和最大部分，例如0.0001或0.9999，我们可能会建议您更好.

The cure is to change Field1 and Field2 from float/single/double to decimal or currency. If you give examples of the smallest and largest values you need to store, including the smallest and largest fractions needed such as 0.0001 or 0.9999, we can possibly advise you better.

请注意，2007年之前的Access版本在使用十进制值的ORDER BY时会遇到问题.请阅读这篇文章的评论，以获得更多关于此的观点.在很多情况下，这对人们来说不是问题，但在其他情况下，则可能是问题.

Be aware that versions of Access before 2007 can have problems with ORDER BY on decimal values. Please read the comments on this post for some more perspective on this. In many cases, this would not be an issue for people, but in other cases it might be.

通常，浮点数应用于最终可能变得过小的或过大的值(小于或大于小数的值可以容纳).您需要了解，浮点数会以某种精度为代价保持更准确的比例.也就是说，小数点将发生溢出或下溢，而浮点数则可以继续下去.但是浮点数只有有限的有效数字，而十进制的数字都是有效的.

In general, float should be used for values that can end up being extremely small or large (smaller or larger than a decimal can hold). You need to understand that float maintains more accurate scale at the cost of some precision. That is, a decimal will overflow or underflow where a float can just keep on going. But the float only has a limited number of significant digits, whereas a decimal's digits are all significant.

如果您不能更改列类型，那么在此期间，您可以通过四舍五入最终计算来解决该问题.直到最后一刻才舍入.

If you can't change the column types, then in the meantime you can work around the problem by rounding your final calculation. Don't round until the very last possible moment.

更新

对我使用十进制的建议的批评已经得到了解决，而不是关于意外的ORDER BY结果的问题，但是在相同位数的情况下，浮点数总体上更加准确.

A criticism of my recommendation to use decimal has been leveled, not the point about unexpected ORDER BY results, but that float is overall more accurate with the same number of bits.

对此事实无可争议.但是，我认为人们使用实际上已经计算或期望以10为基数的价值观来工作更普遍.我在论坛上一遍又一遍地看到有关其浮点数据类型出了什么问题的问题，而我却没有看到同样的关于十进制的问题.对我来说，这意味着人们应该从小数开始，当他们准备好飞跃到如何以及何时使用浮点数时，他们可以学习并在有能力的时候开始使用它.

No contest to this fact. However, I think it is more common for people to be working with values that are in fact counted or are expected to be expressed in base ten. I see questions over and over in forums about what's wrong with their floating-point data types, and I don't see these same questions about decimal. That means to me that people should start off with decimal, and when they're ready for the leap to how and when to use float they can study up on it and start using it when they're competent.

与此同时，虽然人们总是建议十进制当您知道它不那么精确时可能有点令人沮丧，但不要让自己与现实世界相距甚远，在现实世界中，熟悉度会更高四舍五入误差以降低精度为代价是有价值的.

In the meantime, while it may be a tad frustrating to have people always recommending decimal when you know it's not as accurate, don't let yourself get divorced from the real world where having more familiar rounding errors at the expense of very slightly reduced accuracy is of value.

让我指出批评者的例子

十进制(1)/3 * 3 产生 1.999999999999999999999999999

纠正到27个有效数字"，对于所有实际目的都是正确的".

is, in what should be familiar words, "correct to 27 significant digits" which is "correct for all practical purposes."

因此，如果我们有两种处理实际上是在说同一件事的方法，并且它们都可以非常精确地将数字表示为可笑的有效数字，并且都需要四舍五入，但是其中一种已经明显地比其他方法更熟悉的舍入错误，我不能接受推荐一个更熟悉的方法无论如何都是不好的.对于一个可以执行 a-a 而不会得到 0 作为答案的系统的初学者呢?他会感到困惑，并在试图搞怪的同时停下来工作.然后，他将在留言板上寻求帮助，并告诉拍拍答案使用十进制".然后他会再好五年，直到他成长到足以让一天变得好奇，最后学习并真正掌握了float的功能后就可以正确使用它了.

So if we have two ways of doing what is practically speaking the same thing, and both of them can represent numbers very precisely out to a ludicrous number of significant digits, and both require rounding but one of them has markedly more familiar rounding errors than the other, I can't accept that recommending the more familiar one is in any way bad. What is a beginner to make of a system that can perform a - a and not get 0 as an answer? He's going to get confusion, and be stopped in his work while he tries to fathom it. Then he'll go ask for help on a message board, and get told the pat answer "use decimal". Then he'll be just fine for five more years, until he has grown enough to get curious one day and finally studies and really grasps what float is doing and becomes able to use it properly.

也就是说，在最后的分析中，我不得不说，抨击我推荐十进制数似乎在外层空间有点不足.

That said, in the final analysis I have to say that slamming me for recommending decimal seems just a little bit off in outer space.

最后，我想指出的是，以下说法并不完全正确，因为它过于笼统了:

Last, I would like to point out that the following statement is not strictly true, since it overgeneralizes:

浮点型和双精度型类型将数字存储在以2为底的数字中，而不是以10为底.

The float and double types store numbers in base 2, not in base 10.

准确地说，大多数现代系统都存储以2为底的浮点数据类型.但是，并非全部！有些人使用或使用过10.据我所知，有些系统使用3距离 e 更近，因此比2表示具有更好的基数经济性(好像真的很重要).占所有计算机用户的99.999％).另外，说"float和double类型"可能会有点误导，因为双重IS浮动，但float并非双重.Float是浮点数的缩写，但是 Single 和 Double 是float(ing point)子类型，表示总精度.还有单扩展和双扩展浮点数据类型.

To be accurate, most modern systems store floating-point data types with a base of 2. But not all! Some use or have used base 10. For all I know, there are systems which use base 3 which is closer to e and thus has a more optimal radix economy than base 2 representations (as if that really mattered to 99.999% of all computer users). Additionally, saying "float and double types" could be a little misleading, since double IS float, but float isn't double. Float is short for floating-point, but Single and Double are float(ing point) subtypes which connote the total precision available. There are also the Single-Extended and Double-Extended floating point data types.

这篇关于为什么在访问查询中看到-0,000000000000001?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！