为什么将总和乘以其他数字时精度会降低 [英] Why precision is decreasing when multiply sum to other number

问题描述

我在 SQL Server 中遇到了以下错误(或功能).

I have encountered with following bug (or feature) in SQL Server.

当我使用 SUM (*column*) 时，其中 column 具有 numeric(18, 8) 类型并将其与任何其他类型相乘数字(整数或小数)结果精度降低到 numeric(18, 6).

When I use SUM (*column*) where column has a numeric(18, 8) type and multiply it to any other number (integer or decimal) the result precision is reducing to numeric(18, 6).

这是演示的示例脚本.

CREATE TABLE #temp (Qnty numeric(18,8))

INSERT INTO #temp (Qnty) VALUES (0.00000001)
INSERT INTO #temp (Qnty) VALUES (0.00000002)
INSERT INTO #temp (Qnty) VALUES (0.00000003)

SELECT Qnty, 1*Qnty
FROM #temp

SELECT (-1)*SUM(Qnty), SUM(Qnty), -SUM(Qnty), SUM(Qnty) * CAST(2.234 as numeric(18,8))
FROM #temp

DROP TABLE #temp

第二次 SELECT 查询的结果

The result of second SELECT query

0.000000    0.00000006  -0.00000006 0.000000

如你所见，我乘以 SUM，结果是 0.000000

As you can see then I multiply SUM the result is 0.000000

谁能解释这种奇怪的行为?

Could anyone explain the strange behavior?

更新.我在 2000、2005 和 2008 SQL Server 上的 SQL Management Studio 中执行了此查询.

UPD. I executed this query in SQL Management Studio on 2000, 2005 and 2008 SQL Server.

推荐答案

Aggregating a numeric(18, 8) 与 SUM 导致数据类型 numeric(38, 8).

Aggregating a numeric(18, 8) with SUM results in the datatype numeric(38, 8).

可以在此处找到将某个值与数字相乘时如何计算结果数据类型:精度、小数位数和长度 (Transact-SQL)

How the resulting datatype is calculated when multiplying something with numeric can be found here: Precision, Scale, and Length (Transact-SQL)

常量 -1 的数据类型是 numeric(1, 0)

The datatype for your constant -1 is numeric(1, 0)

精度为 p1 + p2 + 1 = 40
比例为 s1 + s2 = 8

Precision is p1 + p2 + 1 = 40
Scale is s1 + s2 = 8

最大精度为 38，剩下的就是 numeric(38, 6).

Max precision is 38 and that leaves you with numeric(38, 6).

在此处详细了解为什么它是 numeric(38, 6):数字的乘法和除法

Read more about why it is numeric(38, 6) here: Multiplication and Division with Numerics

这篇关于为什么将总和乘以其他数字时精度会降低的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！