【问题记录】decimal类型的算术运算注意使用合理的精度

decimal类型的算术运算注意使用合理的精度，容易导致小数精度丢失。

declare @p1 decimal(28, 8), @p2 decimal(38,2)
set @p1 = 1.2345
set @p2 = 1.1

SELECT @p1 + @p2

-- 期望得到 2.3345，实际得到的是2.33

 

https://learn.microsoft.com/zh-cn/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-ver16 

在加法和减法运算中，我们需要 max(p1 - s1, p2 - s2) 个位置来存储十进制数的整数部分。 如果空间不足，无法存储它们，即 max(p1 - s1, p2 - s2) < min(38, precision) - scale，则会减少小数位数以为整数部分提供足够空间。 生成的小数位数是 MIN(precision, 38) - max(p1 - s1, p2 - s2)，因此可能舍入小数部分，使其适合生成的小数位数。

 

数字数据类型的精度和小数位数都是固定的。 如果算术运算符有两个相同类型的表达式，结果就为该数据类型，并且具有对此类型定义的精度和小数位数。 如果运算符有两个不同数字数据类型的表达式，则由数据类型优先级决定结果的数据类型。 结果具有为该数据类型定义的精度和小数位数。

下表定义了当运算结果是 decimal 类型时，如何计算结果的精度和小数位数。 出现以下任一情况时，结果为 decimal 类型：

• 两个表达式都是 decimal 类型。
• 一个表达式是 decimal 类型，而另一个是比 decimal 优先级低的数据类型。

操作数表达式由表达式 e1（精度为 p1，小数位数为 s1）和表达式 e2（精度为 p2，小数位数为 s2）来表示。 非 decimal 类型的任何表达式的精度和小数位数，是对此表达式数据类型定义的精度和小数位数。 函数 max(a,b) 的涵义如下：取 "a" 和 "b" 中较大的值。 同样，min(a,b) 表示取 "a" 和 "b" 中较小的值。

操作	结果精度	结果小数位数 *
e1 + e2	max(s1, s2) + max(p1-s1, p2-s2) + 1	max(s1, s2)
e1 - e2	max(s1, s2) + max(p1-s1, p2-s2) + 1	max(s1, s2)
e1 * e2	p1 + p2 + 1	s1 + s2
e1 / e2	p1 - s1 + s2 + max(6, s1 + p2 + 1)	max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2	max(s1, s2) + max(p1-s1, p2-s2)	max(s1, s2)
e1 % e2	min(p1-s1, p2 -s2) + max( s1,s2 )	max(s1, s2)

* 结果精度和小数位数的绝对最大值为 38。 当结果精度大于 38 时，它会减少到 38，并且相应的小数位数会减少，以尽量避免截断结果的整数部分。 在某些情况下（如乘法或除法），为了保持小数精度，比例因子将不会减少，虽然这可能引发溢出错误。

在加法和减法运算中，我们需要 max(p1 - s1, p2 - s2) 个位置来存储十进制数的整数部分。 如果空间不足，无法存储它们，即 max(p1 - s1, p2 - s2) < min(38, precision) - scale，则会减少小数位数以为整数部分提供足够空间。 生成的小数位数是 MIN(precision, 38) - max(p1 - s1, p2 - s2)，因此可能舍入小数部分，使其适合生成的小数位数。

在乘法和除法运算中，我们需要 precision - scale 个位置来存储结果的整数部分。 可能会使用以下规则减少小数位数：

1. 如果整数部分小于 32，则生成的小数位数减少到 min(scale, 38 - (precision-scale))，因为它不能大于 38 - (precision-scale)。 在这种情况下，结果可能会舍入。
2. 如果小数位数小于 6 且整数部分大于 32，则小数位数将保持不变。 在这种情况下，如果它不适合 decimal(38, scale)，则可能引发溢出错误
3. 如果小数位数大于 6 且整数部分大于 32，则小数位数将设置为 6。 在这种情况下，整数部分和小数位数都会减少，生成的类型是 decimal(38,6)。 结果可能舍入为 6 位小数位数，否则如果整数部分不适合 32 位数字，将引发溢出错误。