执行计算时的比例损失 [英] loss of scale when performing calculation

问题描述

我正在计算，但没有得到预期的答案.我在计算时失去了一些比例.

I'm performing a calculation and I don't get the answer I expect. I lose some scale doing the calculation.

计算是:651/1000 * -413.72063274 = -269.33213191(到 8 d.p)

Calc is: 651/1000 * -413.72063274 = -269.33213191 (to 8 d.p)

在 SQL Server 中，我这样做:

In SQL Server I do this:

declare @var numeric(28,8)
declare @a numeric(28,8)
declare @b numeric(28,8)

set @var = -413.72063274
set @a   = 651.00000000
set @b   = 1000.00000000

select CAST((@a/@b) * @var as numeric(28,8)) as result_1
     , CAST(CAST(@a as numeric(28,8)) 
      /CAST(@b as numeric(28,8)) as numeric(28,8)) 
      *CAST(@var as numeric (28,8))   as result_2

结果是

result_1:-269.33213200(修正为 6dp)
结果_2:-269.332132(更正为6dp)

result_1: -269.33213200 (correct to 6dp)
result_2 : -269.332132 (correct to 6dp)

如何让查询返回:-269.33213191(正确到 8dp)?

How do I get the query to return: -269.33213191 (correct to 8dp)?

推荐答案

对于这种特定情况，您可以先将所有值相乘，然后将最终结果除以这个乘数.

For this specific case, you could cheat by first multiplying all values and dividing the final result back with this multiplier.

declare @var numeric(28,8)
declare @a numeric(28,8)
declare @b numeric(28,8)
declare @m numeric(28,8)

set @var = -413.72063274
set @a   = 651.00000000
set @b   = 1000.00000000
set @m   = 10000000


select CAST(((@a*@m) * (@var*@m) / (@b*@m)) AS NUMERIC(28, 8)) / @m 
-- Result: -269.3321319137

编辑

另一方面，以下保持其精度而不使用乘数

on the other hand, following retains its precision without using a multiplier

select CAST(@a * @var AS NUMERIC(28, 8)) / @b
-- Result: -269.3321319140

这篇关于执行计算时的比例损失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！