执行计算时的比例损失 [英] 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屋!