T-SQL十进制除法精度 [英] T-SQL Decimal Division Accuracy

问题描述

有人知道为什么使用SQLServer 2005

Does anyone know why, using SQLServer 2005

SELECT CONVERT(DECIMAL(30,15),146804871.212533)/CONVERT(DECIMAL (38,9),12499999.9999)

给我11.74438969709659，

gives me 11.74438969709659,

但是当我将分母的小数位增加到15时，我得到的答案就不那么准确了:

but when I increase the decimal places on the denominator to 15, I get a less accurate answer:

SELECT CONVERT(DECIMAL(30,15),146804871.212533)/CONVERT(DECIMAL (38,15),12499999.9999)

给我11.74438969

give me 11.74438969

推荐答案

对于乘法，我们只需将每个参数中的小数位数加在一起(使用笔和纸)即可得出输出的小数位数.

For multiplication we simply add the number of decimal places in each argument together (using pen and paper) to work out output dec places.

但是分裂只会让你大吃一惊.我现在要躺下.

But division just blows your head apart. I'm off to lie down now.

但是在SQL术语中，它完全符合预期.

In SQL terms though, it's exactly as expected.

--Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
--Scale = max(6, s1 + p2 + 1)

--Scale = 15 + 38 + 1 = 54
--Precision = 30 - 15 + 9 + 54 = 72
--Max P = 38, P & S are linked, so (72,54) -> (38,20)
--So, we have 38,20 output (but we don use 20 d.p. for this sum) = 11.74438969709659
SELECT CONVERT(DECIMAL(30,15),146804871.212533)/CONVERT(DECIMAL (38,9),12499999.9999)


--Scale = 15 + 38 + 1 = 54
--Precision = 30 - 15 + 15 + 54 = 84
--Max P = 38, P & S are linked, so (84,54) -> (38,8)
--So, we have 38,8 output = 11.74438969
SELECT CONVERT(DECIMAL(30,15),146804871.212533)/CONVERT(DECIMAL (38,15),12499999.9999)

如果遵循此规则，您可以进行相同的数学运算.同样，如果您将每个数字对都视为

You can do the same math if follow this rule too, if you treat each number pair as

• 146804871.212533000000000和12499999.999900000
• 146804871.212533000000000和12499999.999900000000000

这篇关于T-SQL十进制除法精度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！