将数字数据类型转换为数字时发生算术溢出错误

Question

我在表中有以下列

SIZE NUMERIC(14,5)    
PRICE NUMERIC(14,5)    

当我执行这个select查询时，

SELECT SIZE,
PRICE,
SIZE*PRICE AS TOTAL 
FROM TNAME

我得到了结果：

1.00000 90.00000    90.0000000000
1.00000 90.00000    90.0000000000
1.00000 90.00000    90.0000000000
1.00000 100.00000   100.0000000000
1.00000 30.00000    30.0000000000

我想知道为什么第三列返回小数点后的10位数字？

我也得到

将数字数据类型转换为数字时发生算术溢出错误

同时将结果插入到另一个具有相同列且具有相同数据类型的表中

INSERT INTO TNAME2(SIZE, PRICE, TOTAL)
   SELECT 
       SIZE, PRICE, SIZE * PRICE AS TOTAL 
   FROM 
       TNAME

Answer 1

试试这个。

SELECT SIZE,
PRICE,
CAST(SIZE*PRICE AS numeric(14,5))AS TOTAL 
FROM TNAME

Answer 2

INSERT  INTO TNAME2
SELECT    SIZE ,
Price ,
CAST(SIZE * PRICE AS NUMERIC(15, 5))
FROM      TNAME

或

INSERT  INTO TNAME2 (SIZE,Price,TOTAL)
SELECT    SIZE ,
Price ,
CAST(SIZE * PRICE AS NUMERIC(15, 5)) AS TOTAL
FROM      TNAME

Answer 3

关于第一个问题，小数位数，没有错。这是我们在学校学习的基本的日常乘法：将两个十进制数字相乘，得到的数字的十进制位数与操作数的十进制位数之和相同。整数位数最多可以是操作数的整数位数之和。

CCD_ 2与CCD_ 3相乘产生CCD_。如果SQL Server违反了这个基本规则并任意截断了结果，那将是非常尴尬的。

因此，当您试图将乘积存储在接受较少数字的列中时，会出现溢出错误。SQL Server不会自动更改位数，因为无法决定正确的操作。

根据您愿意遭受的精度损失，有很多方法可以处理：

• 把多余的数字截断（丢掉，最多损失一个单位）。如果你存储总数，这可能会变成一大笔钱
• 四舍五入到所需的位数。听起来很直观，但中途值呢，比如0.00005？它应该是0.0001还是0.0000？所以我们有
• 四舍五入，其中0.5变为1，导致每个条目最多损失0.5
• 向下，当它变为0时，具有相同的损失
• 舍入到偶数或奇数，即舍入到最接近的奇数或偶数，这平均会产生最小的损失。虽然这听起来很奇怪，但它是IEEE 754中定义的标准。它也被称为banker's rounding，因为它用于记账，以最大限度地减少由于截断而造成的损失

如果你想存储更少的数字，你需要决定是否需要截断多余的数字或如何四舍五入，然后在代码中自己完成。

在您的情况下，可以使用CAST作为所需精度的数字。这将执行取整一半，其中0.00005变为0.0001，例如：

INSERT INTO TNAME2(SIZE, PRICE, TOTAL)
SELECT 
   SIZE, PRICE, CAST(SIZE * PRICE as numeric(14,5)) AS TOTAL 
FROM 
   TNAME

SQLFiddle此处

假设位数不超过14，这将起作用，否则您将不得不更改表字段的类型。

如果您希望在SQL中使用其他类型的舍入，则必须创建自己的函数。

Answer 4

获得了以下有用的链接。数字乘法根据此链接，您可以尝试以下的查询

SELECT SIZE,
PRICE,
CAST(SIZE*PRICE AS numeric(28,5))AS TOTAL 
FROM TNAME

Answer 5

试试这个

SELECT SIZE,PRICE,CONVERT(numeric(14,5), SIZE*PRICE) AS TOTAL 
FROM TNAME

在insert中写入相同的查询，它必须工作