小数和bigint分开时如何定义小数位数？ [英] How scale is defined when decimal and bigint are divided?

问题描述

我的值 A 的类型为 DECIMAL（19,8）-小数位数为 8 ，因此将存储在小数点右边的小数位数为 8 。

I have value A of type DECIMAL(19,8) - the scale is 8, so the number of decimal digits that will be stored to the right of the decimal point is 8.

现在，我将 A 除以 B ，其中 B 是 BIGINT 。例如，

Now, I am dividing A on B, where B is BIGINT. For, example:

SELECT CAST(3 AS DECIMAL(19, 8)) / CAST(27 AS BIGINT)               -- 0.111111111111111111111111111
      ,CAST(300 AS DECIMAL(19, 8)) / CAST(27 AS BIGINT)             -- 11.111111111111111111111111111
      ,CAST(75003 AS DECIMAL(19, 8)) / CAST(13664400 AS BIGINT)     -- 0.005488934750153684025643277

输出值的长度为： 29 ， 30 ， 29 。

the output values are with length: 29, 30, 29 respectively.

可以有人告诉我为什么三个部门的价值长度不是 30 吗？ SQL Server 如何计算最终结果的规模？

Could anyone tell why the length of the value for the three divisions is not 30? How the SQL Server is calculating the scale of the final result?

推荐答案

参数1： 3十进制（19，8）

参数2： 27 AS DECIMAL（18，0）-默认精度为18，默认小数位数为0（ BIGINT 转换为 DECIMAL （由于类型优先））

Argument 2: 27 AS DECIMAL (18, 0) -- default precision is 18, default scale is 0 (BIGINT was converted to DECIMAL due to type precedence)

p1 = 19
p2 = 18
s1 = 8
s2 = 0

最大精度=（ p1-s1 + s2）+ MAX（6，s1 + p2 + 1）-最多38

max precision = (p1 - s1 + s2) + MAX(6, s1 + p2 + 1) -- up to 38

最大比例= MAX（6，s1 + p2 + 1）

max scale = MAX(6, s1 + p2 + 1)

让我们计算一下示例1：

Let's calculate for example 1:

precision: (19 - 8 + 0) + MAX(6, 8 + 18 + 1) = 38
scale:     MAX(6, 8 + 18 + 1) = 27

对于您的所有示例，您将始终获得最大27刻度。

For all your examples you will get always max 27 scale.

 0.111111111111111111111111111 (27)
11.111111111111111111111111111 (27)
 0.005488934750153684025643277 (27)

整个部分仅包含必要的数字（1），（2），（1 ）。

The whole part takes only necessary digits (1), (2), (1).

对我来说，一切都是完全有效的。

For me everything is perfectly valid.

此答案基于 < @Paul White a href = http://www.sqlservercentral.com/Forums/Topic870098-338-1.aspx rel = nofollow noreferrer> 除法十进制截断 。

This answer is based on work of @Paul White from Decimal Truncation In division.

这篇关于小数和bigint分开时如何定义小数位数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！