SQL Server十进制运算不正确 [英] SQL Server Decimal Operation is Not Accurate

问题描述

当我在SQL Server中运行此简单操作时:

When I run this simple operation in SQL server:

Select 800.0 /30.0 

我得到的值是26.666666，即使它四舍五入为6位数字，也应该是26.666667.如何获得正确的计算结果?我尝试在线搜索它，并找到了一个解决方案，在该解决方案中，我将每个操作数都转换为高精度的小数，然后再进行操作，但这对我来说并不方便，因为我进行了很多长的复杂计算.认为必须有更好的解决方案.

I get the value 26.666666, where even if it rounds for 6 digits it should be 26.666667. How can I get the calculation to be accurate? I tried to search about it online and I found a solution where I cast each operand to a high precision decimal before the operation, but this will not be convenient for me because I have many long complex calculations. think there must be a better solution.

推荐答案

在使用除法时，在SQL Server中，结果标度之后的任何数字都将被截断，而不是四舍五入.对于您的表达式，您有一个 decimal(4,1​​)和一个 decimal(3,1)，这导致一个 decimal(10,6):

When a using division, in SQL Server, any digits after the resulting scale are truncated, not rounded. For your expression you have a decimal(4,1) and a decimal(3,1), which results in a decimal(10,6):

Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
Scale = max(6, s1 + p2 + 1)

结果， 26.66666666666666〜被截断为 26.666666 .

您可以通过增加精度和小数位数的大小来解决此问题，然后 CONVERT 返回所需的精度和小数位数.例如，将 decimal(3,1)的精度和小数位数提高到 decimal(5,2)，然后转换回 decimal(10,6):

You can get around this by can increasing the size of the precision and scale, and then CONVERT back to your required precision and scale. For example, increase the precision and scale of the decimal(3,1) to decimal(5,2) and convert back to a decimal(10,6):

SELECT CONVERT(decimal(10,6),800.0 / CONVERT(decimal(5,3),30.0));

这将返回 26.666667 .

这篇关于SQL Server十进制运算不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！