SQL 数字数据类型截断数值？

Question

我真的希望那里有一位SQL大神能够协助解决这个问题（如果以前已经回答过，则请原谅。我尝试寻找类似的帖子，但没有找到）:

declare @theanswer numeric(38,16)

select @theanswer = 0.01 / 0.0074464347
select @theanswer

上述结果为1.3429245542165000，但以下内容（在我的看法中看起来相同）

declare @val1 numeric(38,16);
declare @val2 numeric(38,16);

set @val1 = 0.01;
set @val2 = 0.0074464347;

select @val1/@val2

如何处理结果为1.342924并对其进行截断？

有任何想法吗？

- Tom Miller

---

在 Sql-Server 2008 中，我得到了 1.3429245542165000。 - sll

请尝试执行以下语句：select @theanswer = CAST(0.01 as numeric(38,16)) / CAST(0.0074464347 AS numeric(38,16)) - sll

可能是T-SQL十进制除法精度的重复问题。 - Mikael Eriksson

https://dev59.com/z1XTa4cB1Zd3GeqP1nHg#5385417 - Mikael Eriksson

选择 @val1/@val2 作为转换系数。 - Kip Real

Answer 1

为了获得结果(@val1/@val2)的真实精度和范围，我会执行这个T-SQL脚本。

DECLARE @val1 NUMERIC(38,16);
DECLARE @val2 NUMERIC(38,16);

SET @val1 = 0.01;
SET @val2 = 0.0074464347;

SELECT @val1/@val2
        ,SQL_VARIANT_PROPERTY(@val1/@val2, 'BaseType')  [BaseType]
        ,SQL_VARIANT_PROPERTY(@val1/@val2, 'Precision') [Precision]
        ,SQL_VARIANT_PROPERTY(@val1/@val2, 'Scale')     [Scale]

结果将会是：

(No column name)    BaseType    Precision   Scale
1.342924            numeric         38          6

因此，结果精度为38，结果刻度为6。 MSDN有关于算术运算的精度和刻度的详细信息。这些信息可以在此处找到：Precision, Scale, and Length (Transact-SQL)：

Operation = e1 / e2
Result precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
Result scale * = max(6, s1 + p2 + 1)

使用这些公式，我们可以编写下一个T-SQL脚本来获取结果(@val1/@val2)的理论精度和标度：

SELECT   @p1 = 38   --or CONVERT(INT, SQL_VARIANT_PROPERTY(@val1, 'Precision'))
        ,@s1 = 16   --or CONVERT(INT, SQL_VARIANT_PROPERTY(@val1, 'Scale'))
        ,@p2 = 38   --or CONVERT(INT, SQL_VARIANT_PROPERTY(@val2, 'Precision'))
        ,@s2 = 16   --or CONVERT(INT, SQL_VARIANT_PROPERTY(@val2, 'Scale'));

--SELECT    @p1 [@p1], @s1 [@s1], @p2 [@p2], @s2 [@s2];

SELECT  @p_result = @p1 - @s1 + @s2 +   CASE 
                                            WHEN 6 >= @s1 + @p2 + 1 THEN 6 
                                            WHEN 6 < @s1 + @p2 + 1 THEN @s1 + @p2 + 1 
                                        END
        ,@s_result =    CASE 
                            WHEN 6 >= @s1 + @p2 + 1 THEN 6 
                            WHEN 6 < @s1 + @p2 + 1 THEN @s1 + @p2 + 1 
                        END;

SELECT  @p_result [@p_result], @s_result [@s_result];

结果如下：

@p_result   @s_result
93          55

因此，在这个算术操作（@val1/@val2）中，理论上的精度和比例为93和55，但实际的精度和比例为38和6。

实际精度为38，因为"结果的精度和比例绝对最大值为38"。 关于实际结果比例（6）MSDN不是很清楚:"当结果精度大于38时，相应的比例会减小，以防止结果的整数部分被截断"。

为了看到"相应的比例如何减少"，我执行了上述测试（脚本1用于真实精度和比例，脚本2用于理论精度和比例），使用具有相同比例（16）但不同比例（从16到38）的NUMERIC值。这些测试的结果如下：

/*
Result prec. Result scale   (T=theoretical value, R=real value)
 T-R         T-R            --@val1 and @val2 data type
49-38       33-22           --NUMERIC(16, 16)
51-38       34-21           --NUMERIC(17, 16)
53-38       35-20           --NUMERIC(18, 16)
55-38       36-19           --NUMERIC(19, 16)
...
61-38       39-16           --NUMERIC(22, 16) -- <-- data type for [real] result scale 16
...                         
77-38       47-8            --NUMERIC(30, 16)
79-38       48-7            --NUMERIC(31, 16)
81-38       49-6            --NUMERIC(32, 16)
83-38       50-6            --NUMERIC(33, 16)
85-38       51-6            --NUMERIC(34, 16)
...
93-38       55-6            --NUMERIC(38, 16)
*/

对这些结果进行分析：

1.我看到实际结果范围的等差级数是：从22到6，步长为-1。

2.此外，如果@val1和@val2的比例尺是恒定的(NUMERIC(...,16))，则@val1和@val2精度(从16到32)与[实际]结果刻度(从16到6)之间存在反相关关系。

3.如果@val1和@val2的精度为32或更高(NUMERIC(32->38,16))，则[实际]结果刻度始终为6 => 这是您的情况。

4.如果需要更大的[实际]结果刻度(超过6)，则需要使用较低的@val1和@val2精度：NUMERIC(22, 16):

SELECT          
         CONVERT(NUMERIC(22,16),@val1) / CONVERT(NUMERIC(22,16),@val2) [CONVERT(NUMERIC(22,16)]
        ,SQL_VARIANT_PROPERTY( CONVERT(NUMERIC(22,16),@val1) / CONVERT(NUMERIC(22,16),@val2) , 'BaseType')  [BaseType]
        ,SQL_VARIANT_PROPERTY( CONVERT(NUMERIC(22,16),@val1) / CONVERT(NUMERIC(22,16),@val2) , 'Precision') [Precision]
        ,SQL_VARIANT_PROPERTY( CONVERT(NUMERIC(22,16),@val1) / CONVERT(NUMERIC(22,16),@val2) , 'Scale')     [Scale] 

CONVERT(NUMERIC(22,16) BaseType Precision Scale
---------------------- -------- --------- -----
1.3429245542165299     numeric  38        16

- Bogdan Sahlean

---

我只是一个 SQL Server 爱好者，没有别的。 - Bogdan Sahlean