sql-server - 执行计算时的比例损失

Question

我正在执行计算，但没有得到预期的答案。我在计算时丢失了一些比例。

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

在 SQL Server 中我这样做:

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)
result_2 : -269.332132 (正确为 6dp)

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

Answer 1

乘法和除法的十进制到十进制转换规则are described in BOL .

*The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

但没有具体说明这种截断是如何执行的。 This is documented here .然而，有时只是使用试错法更容易!

以下中间转换会为您提供所需的结果。你能忍受这些吗？

DECLARE @var NUMERIC(19,8)
DECLARE @a NUMERIC(19,8)
DECLARE @b NUMERIC(19,8)

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

DECLARE @v SQL_VARIANT 
SET @v =  CAST(@a/@b AS NUMERIC(24,10))* CAST(@var AS NUMERIC(23,8))

SELECT    CAST(SQL_VARIANT_PROPERTY(@v, 'BaseType') AS VARCHAR(30)) AS BaseType,    
          CAST(SQL_VARIANT_PROPERTY(@v, 'Precision') AS INT) AS PRECISION,    
          CAST(SQL_VARIANT_PROPERTY(@v, 'Scale') AS INT) AS Scale