SQL Server 奇怪的天花板()行为 [英] SQL Server Strange Ceiling() behavior

问题描述

谁能解释一下 SQL Server 中的以下结果?我被难住了.

Can anyone explain the following results in SQL Server? I'm stumped.

declare @mynum float = 8.31

select ceiling( @mynum*100)

结果为 831

declare @mynum float = 8.21

select ceiling( @mynum*100)

结果为 822

我已经测试了整个范围的数字(在 SQL Server 2012 中).一些增加，而另一些保持不变.我不明白为什么天花板会以不同的方式对待其中的一些.从 float 更改为 decimal(18,5) 似乎可以解决问题，但我担心这样做可能会遗漏其他影响.任何解释都会有所帮助.

I've tested a whole range of numbers (in SQL Server 2012). Some increase while others stay the same. I'm at a loss understanding why ceiling is treating some of them differently. Changing from a float to a decimal(18,5) seems to fix the problem but I'm wary there may be other repercussions I'm missing from doing so. Any explanations would help.

推荐答案

我认为这叫做浮点精度.你可以在几乎所有的编程语言和数据库中找到它.这是因为数据仅以某种精度存储，实际上您设置的 8.31 可能不是 8.31 但例如 8.31631312381813 和乘法时it 和 ceil it 可能会导致出现不同的值.

I think this is called float precision. You can find it in almost all programming languages and in Database too. This is because data is stored only with some precision and in fact what you set as 8.31 is probably not 8.31 but for example 8.31631312381813 and when multiply it and ceil it may cause that different value appear.

在 SQL 服务器文档页面 ，您可以阅读:

用于浮点数值数据的近似数数据类型.浮点数据是近似值；因此，并非数据类型范围内的所有值都可以准确表示.

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

在其他数据库系统中也存在同样的问题.例如在 mysql 网站，您可以阅读:

In other database systems the same problem exists. For example at mysql website you can read:

浮点数有时会引起混淆，因为它们是近似值，而不是存储为精确值.SQL 语句中写入的浮点值可能与内部表示的值不同.尝试在比较中将浮点值视为精确值可能会导致问题.它们还受制于平台或实现依赖性.FLOAT 和 DOUBLE 数据类型受这些问题的影响.对于 DECIMAL 列，MySQL 以 65 位十进制数字的精度执行操作，这应该可以解决大多数常见的不准确问题.

Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values. A floating-point value as written in an SQL statement may not be the same as the value represented internally. Attempts to treat floating-point values as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. The FLOAT and DOUBLE data types are subject to these issues. For DECIMAL columns, MySQL performs operations with a precision of 65 decimal digits, which should solve most common inaccuracy problems.

这篇关于SQL Server 奇怪的天花板()行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！