Sql Server Decimal(30,10)丢失最后两个小数。

When 2 decimal(30,10) numbers are divided in Sql Server 05, 2 last decimals seem to be getting lost (not even rounded off, simply truncated).

当2个小数(30,10)在Sql Server 05中被划分时，最后一个小数看起来会丢失(甚至没有被删除，只是被截断)。

For example:

例如:

Declare @x decimal(30,10)
Declare @y decimal(30,10)
Declare @z decimal(30,10)

select @x = 2.1277164747 
select @y = 4.8553794574

Select @z = @y/@x   
select @z

Result: 2.2819673100

结果:2.2819673100

But if 2 numbers being divided are converted to float that seems to work:

但是，如果两个数字被分割，就会被转换为浮点数。

....
Select @z = cast(@y as float)/cast(@x as float)
select @z

Result: 2.2819673181

结果:2.2819673181

Why is Sql doing this? And what's the right way of dividing decimals without loosing precision in Sql.

为什么Sql要这么做?在Sql中，什么才是正确的小数除法?

2 个解决方案

#1

The maximum precision allowed in SQL Server is 38. You are using Decimal(30,10). The max value is 99,999,999,999,999,999,999.9999999999 if you divide this number by 0.000000001, you will end up with an even bigger number, so the resulting data type must be able to accommodate it. This causes you to lose some precision.

SQL Server中允许的最大精度为38。您正在使用十进制(30、10)。如果你将这个数字除以0.000000001，你就会得到一个更大的数字，因此得到的数据类型必须能够容纳它。这会使你失去一些精确度。

Change your original data types to Decimal(20,10) and this problem does not occur.

将原始数据类型更改为Decimal(20,10)，此问题不会发生。

For full rules regarding data types (and how they are affected by math operations):

关于数据类型的完整规则(以及它们如何受到数学操作的影响):

Full rules here

完整的规则在这里

#2

-1

In short, use casting to guarantee your results. When you assign @x and @y to literal values, they are probably adopting the precision of those literals. This helps to explain why division by those values comes up short of your expectations.

简而言之，使用铸造来保证你的结果。当您将@x和@y分配给文字值时，它们可能会采用这些文字的精度。这有助于解释为什么这些价值的划分比你的期望差。

#1