SQL Server舍入错误，给出不同的值 [英] SQL Server rounding Error, Giving different values

问题描述

我有一个存储过程，该过程进行了大量计算，并将结果存储在几个临时表中. 最后计算总和并四舍五入到小数点后两位，并存储在临时表中并选择该值.

I have a Stored Procedure that does a lots of calculation, stores the results in several temporary table. Finally calculating the sum and rounding to two decimal and stores in a temporary table and selects that.

所有中间和最终临时表的关注列的数据类型为float.

All the intermediate and final temporary table has datatype float for the column of concern.

原始方案:

Declare @Intermediate table
{
 --several other columns

Labor float

--several other columns
};

---Lots of calculation ---xx-----

Declare @Final table
{
 --several other columns

LaborTotal float

--several other columns
};

INSERT INTO @Final  SELECT ROUND(ISNULL((SELECT SUM([Labor]) FROM @Intermediate ),0),2)  AS LaborTotal;

SELECT * FROM @Final;

Result: 7585.22  --> when rounded  //Here is the error Expecting 7585.23
        7585.225 --> when not rounded

测试案例:

   DECLARE @test float = 7585.225;
   SELECT ROUND(@test,2) AS Result; --> results 7585.23

   SELECT ROUND(7585.225,2) AS Result --> results 7585.23

将各个值插入临时表，然后计算总和

Inserted individual values to a temporary table, and then calculated the sum

DECLARE @TmpTable table
(
 MaterialAmount float
 ,LaborAmount float
);

INSERT INTO @TmpTable VALUES (12.10,1218.75);
INSERT INTO @TmpTable VALUES (12.10,1090.125);
INSERT INTO @TmpTable VALUES (12.10,900);
INSERT INTO @TmpTable VALUES (12.10,1632.6);
INSERT INTO @TmpTable VALUES (12.10,1625);
INSERT INTO @TmpTable VALUES (12.10,1118.75);


SELECT ROUND(ISNULL((SELECT SUM(MaterialAmount) FROM @TmpTable), 0),2) AS MatSum,
       ISNULL((SELECT SUM(LaborAmount) FROM @TmpTable), 0) AS LabSumUnrounded, --> 7585.225
       ROUND(ISNULL((SELECT SUM(LaborAmount) FROM @TmpTable), 0),2) AS LabSum;  --> 7585.23

SELECT ROUND(SUM(MaterialAmount),2),
       ROUND(SUM(LaborAmount),2)   ---> 7585.23
   FROM @TmpTable;

任何想法/建议为什么我在原始方案中获得0.01的差异，而在所有测试用例中获得确切的值? 预先感谢.

Any idea/suggestion why i am getting 0.01 difference in my original scenario, while getting exact values in all my testcases ? Thanks in advance.

推荐答案

这是因为您使用的是float数据库类型.

This is because you are using float database type.

浮点数不能用于表示需要精度的值，因为它们被存储为近似值，因此不同的操作可以为您提供不同的结果.

Float should not be used to represent values that require precision, since they are stored as approximations, different manipulations can give you different results.

在sql server中，可以将十进制和数字数据类型用于数字精度: http://msdn.microsoft.com/en-us/library/ms187746.aspx

In sql server you can use decimal and numeric data types for numerical precision: http://msdn.microsoft.com/en-us/library/ms187746.aspx

这篇关于SQL Server舍入错误，给出不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！