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屋！