Sql Server Decimal(30,10) 丢失最后 2 位小数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1455547/
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
Sql Server Decimal(30,10) losing last 2 decimals
提问by WebMatrix
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).
当 Sql Server 05 中的 2 个十进制 (30,10) 数字被除时，最后 2 个小数似乎丢失了（甚至没有四舍五入，只是被截断）。
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.28196731 00
But if 2 numbers being divided are converted to float that seems to work:
但是，如果将被除的 2 个数字转换为似乎有效的浮点数：
....
Select @z = cast(@y as float)/cast(@x as float)
select @z
Result: 2.2819673181
结果：2.28196731 81
Why is Sql doing this? And what's the right way of dividing decimals without loosing precision in Sql.
为什么 Sql 会这样做？什么是在 Sql 中不丢失精度的小数除法的正确方法。
回答by George Mastros
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。您使用的是 Decimal(30,10)。最大值是 99,999,999,999,999,999,999.9999999999 如果你把这个数字除以 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):
有关数据类型的完整规则（以及它们如何受数学运算的影响）：
回答by David Andres
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 分配给文字值时，它们可能会采用这些文字的精度。这有助于解释为什么除以这些值没有达到您的预期。
