SQL SERVER 中 Decimal 相乘，结果被截断后的处理方式

最新推荐文章于 2023-08-16 16:35:30 发布

qq_39459229

最新推荐文章于 2023-08-16 16:35:30 发布

阅读量3.1k

点赞数 2

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_39459229/article/details/101542475

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

SQL SERVER 中 Decimal 相乘，结果被截断后的处理方式

条件：

select cast(1.0 as decimal(28,10)) * cast(1.1234567890 as decimal(28,10))

输出的结果： 1.123457
需要的结果： 1.1234567890
问题来了： 为什么两个相同的decimal（28,10）类型相乘只截取到了小数点后六位？

先给出此问题的解决方案：

--问题：结果要保留10位小数，不能被截断，且已知其中两数相除不会大于1
select cast(1.1234567890 as decimal(28,10))/cast(1.9876543210 as decimal(28,10)) * cast(123456789012345678.1234567890 as decimal(28,10)) 
--解决方案1：
select cast(cast(1.1234567890 as decimal(28,10))/cast(1.9876543210 as decimal(28,10))*10000000000 * cast(123456789012345678.1234567890 as decimal(28,10)) as decimal(28,0))/10000000000
--结果：69779924149758454.2753623187		最终类型为decimal(38,10)不会截断
--说明：已知条件cast(1.1234567890 as decimal(28,10))/cast(1.9876543210 as decimal(28,10))结果不会超过1，所以*10000000000不会溢出，然后再计算计算相乘，再除以10000000000得到保留十位小数的数值。
--解决方案2
select cast(cast(1.9876543210 as decimal(28,10))/cast(1.1234567890 as decimal(28,10)) as decimal(23,10)) * cast(1234567.1234567890 as decimal(23,10)) 
--结合此问题的实际情况，数值不会超过decimal（23，10），所以转为decimal（23，10）可以使他们的相乘能保留11位小数，刚好能满足保留10位小数的要求。

在网上逛了很久，结合微软给出的文档和网友的结论解决了decimal两数相乘被截断为6位小数的问题。

先给出微软的说明：

精度指数字的位数。小数位数指小数点后的数字位数。例如，数 123.45 的精度是 5，小数位数是 2。
在 SQL Server 中，numeric 和 decimal 数据类型的默认最大精度为 38 。在 SQL Server 早期版本中，默认最大精度为 28。

数字数据类型的长度是存储此数所占用的字节数。字符串或 Unicode 数据类型的长度是字符个数。 binary、varbinary 和 image 数据类型的长度是字节数。例如，int 数据类型可以有 10 位数，用 4 个字节存储，不接受小数点。 int 数据类型的精度是 10，长度是 4，小数位数是 0 。

当 char、varchar、binary 和 varbinary 表达式中的两种相串联时，所生成表达式的长度是两个源表达式长度之和（不超过 8,000 个字符）。

当两个 nchar 或 nvarchar 表达式相串联时，所生成表达式的长度是两个源表达式长度之和（不超过 4,000 个字符）。

使用 UNION、EXCEPT 或 INTERSECT 对数据类型相同但长度不同的两个表达式进行比较时，得到的长度为两个表达式中较大的长度。

除了 decimal 类型之外，数字数据类型的精度和小数位数都是固定的。如果算术运算符有两个相同类型的表达式，结果就为该数据类型，并且具有对此类型定义的精度和小数位数。如果运算符有两个不同数字数据类型的表达式，则由数据类型优先级决定结果的数据类型。结果具有为该数据类型定义的精度和小数位数。
下表定义了当运算结果是 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
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 个位置来存储结果的整数部分。可能会使用以下规则减少小数位数：

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

示例

下面的表达式返回结果 0.00000090000000000（未舍入），因为结果适合 decimal(38,17)：

	select cast(0.0000009000 as decimal(30,20)) * cast(1.0000000000 as decimal(30,20)) [decimal 38,17]

在这种情况下，精度为 61 (p1+p2+1)，小数位数为 40 (s1+s2)。整数部分 (precision-scale = 21) 小于 32，因此这是乘法规则中的情况 (1)，小数位数计算为 min(scale, 38 - (precision-scale)) = min(40, 38 - (61-40)) = 17。结果类型是 decimal(38,17)。

下面的表达式返回结果 0.000001 以适合 decimal(38,6)：

select cast(0.0000009000 as decimal(30,10)) * cast(1.0000000000 as decimal(30,10)) [decimal(38, 6)]

在这种情况下，精度为 61 (p1+p2+1)，小数位数为 20 (s1+s2)。小数位数大于 6，整数部分 (precision-scale = 41) 大于 32。这是乘法规则中的情况 (3)，结果类型是 decimal(38,6)。

结合网友的理解：

截断原因：decimal类型最大精度为38位，而两个decimal数相乘结果可能会超过38位，为避免截断整数部分，也要保证结果的精确性，在截断时有以下三个条件，如果不满足则提示溢出报错

A)小数位截去后至少6，直接截小数。对应微软给出的条件1

如果整数部分小于 32，则生成的小数位数减少到 min(scale, 38 - (precision-scale))，因为它不能大于 38 - (precision-scale)。在这种情况下，结果可能会舍入。

	条件：(s1+s2) - (p1+p2+1-38) >> 6
	结果：p3 = 38, s3 = (s1+s2) - (p1+p2+1-38)

B)小数位不到6，保留全部小数位。对应微软给出的条件2

如果小数位数小于 6 且整数部分大于 32，则小数位数将保持不变。在这种情况下，如果它不适合 decimal(38, scale)，则可能引发溢出错误*

	条件：(s1+s2) < 6
	结果：p3 = 38, s3 = s1+s2

C)小数位截去后不足6，固定保留6个小数位。

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

	条件：(s1+s2) - (p1+p2+1-38) < 6
	结果：p3 = 38, s3 = 6

注：操作数表达式由表达式 e1（精度为 p1，小数位数为 s1）和表达式 e2（精度为 p2，小数位数为 s2）来表示。

测试

SELECT SQL_VARIANT_PROPERTY(v,'BaseType') BaseType,
       SQL_VARIANT_PROPERTY(v,'Precision') [Precision],
       SQL_VARIANT_PROPERTY(v,'Scale') Scale,
       v
  FROM (
		-- 方式A
		-- A)小数位截去后至少6，直接截小数。
		-- 条件：(s1+s2) - (p1+p2+1-38) >> 6
		-- 结果：p3 = 38, s3 = (s1+s2) - (p1+p2+1-38)
		
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,23)) * cast(1.0 as decimal(38,23)) ) AS v		--1
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(28,23)) * cast(1.0 as decimal(28,23)) ) AS v		--2
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(28,15)) * cast(1.0 as decimal(28,15)) ) AS v		--3
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(28,16)) * cast(1.0 as decimal(28,16)) ) AS v		--4
        -- 方式A补充
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,25)) ) AS v		--5
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,26)) ) AS v		--6
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,27)) ) AS v		--7
        -- 方式B
		-- B)相乘后小数位不到6，保留全部小数位。
		-- 条件：(s1+s2) < 6		
		-- 结果：p3 = 38, s3 = s1+s2
		UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,0)) * cast(1.0 as decimal(38,0)) ) AS v		--8
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,0)) * cast(1.0 as decimal(38,1)) ) AS v		--9
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,1)) * cast(1.0 as decimal(38,1)) ) AS v		--10
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,1)) * cast(1.0 as decimal(38,2)) ) AS v		--11
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,1)) * cast(1.0 as decimal(38,3)) ) AS v		--12
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,1)) * cast(1.0 as decimal(38,4)) ) AS v		--13
        -- 方式C
		-- C)小数位截去后不足6，固定保留6个小数位。
		-- 条件：(s1+s2) - (p1+p2+1-38) < 6		
		-- 结果：p3 = 38, s3 = 6
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,1)) * cast(1.0 as decimal(38,5)) ) AS v		--14
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,1)) * cast(1.0 as decimal(38,6)) ) AS v		--15
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(28,3)) * cast(1.0 as decimal(28,6)) ) AS v		--16
        -- 方式C补充
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,19)) ) AS v		--17
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,20)) ) AS v		--18
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,21)) ) AS v		--19
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,22)) ) AS v		--20
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,23)) ) AS v		--21
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,24)) ) AS v		--22
       ) T

方式A

BaseType    Precision   Scale   v
----------- ----------- ------- ---------------------------------------
decimal     38          7       1.0000000
decimal     38          27      1.000000000000000000000000000
decimal     38          11      1.00000000000
decimal     38          13      1.0000000000000

p1+p2+1-38 = 38+38+1-38 = 39
s1+s2 = 23+23 = 46
s3 = 46-39 = 7
p1+p2+1-38 = 28+28+1-38 = 19
s1+s2 = 23+23 = 46
s3 = 46-19 = 27
p1+p2+1-38 = 28+28+1-38 = 19
s1+s2 = 15+15 = 30
s3 = 30-19 = 11
p1+p2+1-38 = 28+28+1-38 = 19
s1+s2 = 16+16 = 32
s3 = 30-19 = 13

方式A补充

BaseType    Precision   Scale   v
----------- ----------- ------- ---------------------------------------
decimal     38          6       1.000000
decimal     38          7       1.0000000
decimal     38          8       1.00000000

p1+p2+1-38 = 38+38+1-38 = 39
s1+s2 = 20+25 = 45
s3 = 45-39 = 6
p1+p2+1-38 = 38+38+1-38 = 39
s1+s2 = 20+26 = 46
s3 = 46-39 = 7
p1+p2+1-38 = 38+38+1-38 = 39
s1+s2 = 20+27 = 47
s3 = 47-39 = 8

方式B

BaseType    Precision   Scale   v
----------- ----------- ------- ---------------------------------------
decimal     38          0       1
decimal     38          1       1.0
decimal     38          2       1.00
decimal     38          3       1.000
decimal     38          4       1.0000
decimal     38          5       1.00000

s3 = s1+s2 = 0+0 = 0
s3 = s1+s2 = 0+1 = 1
s3 = s1+s2 = 1+1 = 2
s3 = s1+s2 = 1+2 = 3
s3 = s1+s2 = 1+3 = 4
s3 = s1+s2 = 1+4 = 5

方式C

BaseType    Precision   Scale   v
----------- ----------- ------- ---------------------------------------
decimal     38          6       1.000000
decimal     38          6       1.000000
decimal     38          6       1.000000

p1+p2+1-38 = 38+38+1-38 = 39
s1+s2 = 1+5 = 6
s3 = 6-39 < 6 → s3 = 6
p1+p2+1-38 = 38+38+1-38 = 39
s1+s2 = 1+6 = 7
s3 = 7-39 < 6 → s3 = 6
p1+p2+1-38 = 28+28+1-38 = 19
s1+s2 = 3+6 = 9
s3 = 9-19 < 6 → s3 = 6

方式C补充

BaseType    Precision   Scale   v
----------- ----------- ------- ---------------------------------------
decimal     38          6       1.000000
decimal     38          6       1.000000
decimal     38          6       1.000000
decimal     38          6       1.000000
decimal     38          6       1.000000
decimal     38          6       1.000000

p1+p2+1-38 = 38+38+1-38 = 39
s1+s2 = 20+19 = 39
s3 = 39-39 < 6 → s3 = 6
p1+p2+1-38 = 38+38+1-38 = 39
s1+s2 = 20+20 = 40
s3 = 40-39 < 6 → s3 = 6
p1+p2+1-38 = 38+38+1-38 = 39
s1+s2 = 20+21 = 41
s3 = 41-39 < 6 → s3 = 6
p1+p2+1-38 = 38+38+1-38 = 39
s1+s2 = 20+22 = 42
s3 = 42-39 < 6 → s3 = 6
p1+p2+1-38 = 38+38+1-38 = 39
s1+s2 = 20+23 = 43
s3 = 43-39 < 6 → s3 = 6
p1+p2+1-38 = 38+38+1-38 = 39
s1+s2 = 20+24 = 44
s3 = 44-39 < 6 → s3 = 6

关于有效位个数

当不需要截断时（p1+p2+1<=38），总有效位数只能利用到37。
而进行截断时，38个有效位都能利用起来。

SELECT SQL_VARIANT_PROPERTY(v,'BaseType') BaseType,
       SQL_VARIANT_PROPERTY(v,'Precision') [Precision],
       SQL_VARIANT_PROPERTY(v,'Scale') Scale,
       v
  FROM (
        SELECT CONVERT(sql_variant, cast(POWER(10.0,18) as decimal(20,0)) * cast(POWER(10.0,19) as decimal(20,0)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(POWER(10.0,17) as decimal(38,1)) * cast(POWER(10.0,18) as decimal(38,1)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(POWER(10.0,15) as decimal(38,6)) * cast(POWER(10.0,16) as decimal(38,6)) ) AS v
       ) T

BaseType    Precision   Scale   v
----------- ----------- ------- ---------------------------------------
decimal     38          0       10000000000000000000000000000000000000
decimal     38          2       100000000000000000000000000000000000.00
decimal     38          6       10000000000000000000000000000000.000000

关于溢出

只要对整数有截断（方式B、C），就存在溢出的可能。
下面的语句都会出溢出错误。

SELECT CONVERT(sql_variant, cast(POWER(10.0,19) as decimal(20,0)) * cast(POWER(10.0,19) as decimal(20,0)) ) AS v
SELECT CONVERT(sql_variant, cast(POWER(10.0,18) as decimal(38,1)) * cast(POWER(10.0,18) as decimal(38,1)) ) AS v
SELECT CONVERT(sql_variant, cast(POWER(10.0,16) as decimal(38,6)) * cast(POWER(10.0,16) as decimal(38,6)) ) AS v