为什么在SQL中为199.96-0 = 200? [英] Why is 199.96 - 0 = 200 in SQL?
问题描述
我有一些客户收到奇怪的账单.我能够找出核心问题:
I have some clients getting weird bills. I was able to isolate the core problem:
SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 200 what the?
SELECT 199.96 - (0.0 * FLOOR(1.0 * CAST(199.96 AS DECIMAL(19, 4)))) -- 199.96
SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * 199.96)) -- 199.96
SELECT 199.96 - (CAST(0.0 AS DECIMAL(19, 4)) * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 199.96
SELECT 199.96 - (CAST(0.0 AS DECIMAL(19, 4)) * FLOOR(1.0 * CAST(199.96 AS DECIMAL(19, 4)))) -- 199.96
SELECT 199.96 - (CAST(0.0 AS DECIMAL(19, 4)) * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * 199.96)) -- 199.96
-- It gets weirder...
SELECT (0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 0
SELECT (0 * FLOOR(1.0 * CAST(199.96 AS DECIMAL(19, 4)))) -- 0
SELECT (0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * 199.96)) -- 0
-- so... ... 199.06 - 0 equals 200... ... right???
SELECT 199.96 - 0 -- 199.96 ...NO....
有人知道吗，这到底是怎么回事?我的意思是，它肯定与十进制数据类型有关，但我实在无法绕开它……
Has anyone a clue, what the heck is happening here? I mean, it has certainly something to do with the decimal datatype, but I can't really wrap my head around it...
关于数字文字是什么数据类型，这引起了很多困惑，所以我决定显示实线:
There was a lot of confusion about of what datatype the number literals were, so I decided to show the real line:
PS.SharePrice - (CAST((@InstallmentCount - 1) AS DECIMAL(19, 4)) * CAST(FLOOR(@InstallmentPercent * PS.SharePrice) AS DECIMAL(19, 4))))
PS.SharePrice DECIMAL(19, 4)
@InstallmentCount INT
@InstallmentPercent DECIMAL(19, 4)
我确保在将其应用到外部上下文之前，显式强制转换每个操作数具有不同于DECIMAL(19, 4)类型的结果.
I made sure that the result of each operation having an operand of a type different than DECIMAL(19, 4) is cast explicitly before applying it to the outer context.
尽管如此，结果仍然是200.00.
Nevertheless, the result remains 200.00.
我现在创建了一个精简的示例，您可以在计算机上执行.
I have now created a boiled down sample you guys can execute on your computer.
DECLARE @InstallmentIndex INT = 1
DECLARE @InstallmentCount INT = 1
DECLARE @InstallmentPercent DECIMAL(19, 4) = 1.0
DECLARE @PS TABLE (SharePrice DECIMAL(19, 4))
INSERT INTO @PS (SharePrice) VALUES (599.96)
-- 2000
SELECT
IIF(@InstallmentIndex < @InstallmentCount,
FLOOR(@InstallmentPercent * PS.SharePrice),
1999.96)
FROM @PS PS
-- 2000
SELECT
IIF(@InstallmentIndex < @InstallmentCount,
FLOOR(@InstallmentPercent * CAST(599.96 AS DECIMAL(19, 4))),
1999.96)
FROM @PS PS
-- 1996.96
SELECT
IIF(@InstallmentIndex < @InstallmentCount,
FLOOR(@InstallmentPercent * 599.96),
1999.96)
FROM @PS PS
-- Funny enough - with this sample explicitly converting EVERYTHING to DECIMAL(19, 4) - it still doesn't work...
-- 2000
SELECT
IIF(@InstallmentIndex < @InstallmentCount,
FLOOR(@InstallmentPercent * CAST(199.96 AS DECIMAL(19, 4))),
CAST(1999.96 AS DECIMAL(19, 4)))
FROM @PS PS
现在我有东西...
Now I've got something...
-- 2000
SELECT
IIF(1 = 2,
FLOOR(CAST(1.0 AS decimal(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))),
CAST(1999.96 AS DECIMAL(19, 4)))
-- 1999.9600
SELECT
IIF(1 = 2,
CAST(FLOOR(CAST(1.0 AS decimal(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))) AS INT),
CAST(1999.96 AS DECIMAL(19, 4)))
到底该怎么办?地板应该返回一个整数.这里发生了什么? :-D
What the hell - floor is supposed to return an integer anyway. What's going on here? :-D
我想我现在已经将它真正地精炼为-D
I think I now managed to really boil it down to the very essence :-D
-- 1.96
SELECT IIF(1 = 2,
CAST(1.0 AS DECIMAL (36, 0)),
CAST(1.96 AS DECIMAL(19, 4))
)
-- 2.0
SELECT IIF(1 = 2,
CAST(1.0 AS DECIMAL (37, 0)),
CAST(1.96 AS DECIMAL(19, 4))
)
-- 2
SELECT IIF(1 = 2,
CAST(1.0 AS DECIMAL (38, 0)),
CAST(1.96 AS DECIMAL(19, 4))
)
推荐答案
我需要首先对此展开一些内容，以便了解发生了什么事情:
I need to start by unwrapping this a bit so I can see what's going on:
SELECT 199.96 -
(
0.0 *
FLOOR(
CAST(1.0 AS DECIMAL(19, 4)) *
CAST(199.96 AS DECIMAL(19, 4))
)
)
现在，让我们确切地了解一下SQL Server在减法操作的每一侧使用什么类型:
Now let's see exactly what types SQL Server is using for each side of the subtraction operation:
SELECT SQL_VARIANT_PROPERTY (199.96 ,'BaseType'),
SQL_VARIANT_PROPERTY (199.96 ,'Precision'),
SQL_VARIANT_PROPERTY (199.96 ,'Scale')
SELECT SQL_VARIANT_PROPERTY (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))) ,'BaseType'),
SQL_VARIANT_PROPERTY (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))) ,'Precision'),
SQL_VARIANT_PROPERTY (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))) ,'Scale')
结果:
numeric 5 2
numeric 38 1
所以199.96是numeric(5,2)，而更长的Floor(Cast(etc))是numeric(38,1).
用于减法运算(即:e1 - e2)的结果精度和小数位数的规则如下:
The rules for the resulting precision and scale of a subtraction operation (ie: e1 - e2) look like this:
精度:max(s1，s2)+ max(p1-s1，p2-s2)+1
比例:最大(s1，s2)
Precision: max(s1, s2) + max(p1-s1, p2-s2) + 1
Scale: max(s1, s2)
这样评估:
精度: max(1,2)+ max(38-1，5-2)+1 => 2 + 37 +1 => 40
比例:最大(1,2)=> 2
Precision: max(1,2) + max(38-1, 5-2) + 1 => 2 + 37 + 1 => 40
Scale: max(1,2) => 2
您还可以使用规则"链接首先找出numeric(38,1)的来源(提示:您将两个精度19值相乘).
You can also use the rules link to figure out where the numeric(38,1) came from in the first place (hint: you multiplied two precision 19 values).
但是:
- 结果精度和小数位数的绝对最大值为38.当结果精度大于38时，它将减小为38，并且 缩小相应规模，以防止 被截断的结果.在某些情况下，例如乘法或 除法，为了保持小数，比例因子将不会减少 精度，尽管会增加溢出错误.
- The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it is reduced to 38, and the corresponding scale is reduced to try to prevent the integral part of a result from being truncated. In some cases such as multiplication or division, scale factor will not be reduced in order to keep decimal precision, although the overflow error can be raised.
糟糕.精度为40.我们必须降低精度，因为降低精度应始终截断最低有效位数，这也意味着缩小比例. 表达式的最终结果类型为numeric(38,0)，对于199.96，该类型四舍五入为200.
Oops. The precision is 40. We have to reduce it, and since reducing precision should always cut off the least significant digits that means reducing scale, too. The final resulting type for the expression will be numeric(38,0), which for 199.96 rounds to 200.
您可以通过在整个表达式结果周围将CAST()操作从大表达式内部移动并合并到 one CAST()来解决此问题.因此:
You can probably fix this by moving and consolidating the CAST() operations from inside the large expression to one CAST() around the entire expression result. So this:
SELECT 199.96 -
(
0.0 *
FLOOR(
CAST(1.0 AS DECIMAL(19, 4)) *
CAST(199.96 AS DECIMAL(19, 4))
)
)
成为:
SELECT CAST( 199.96 - ( 0.0 * FLOOR(1.0 * 199.96) ) AS decimial(19,4))
我什至还可以移除外部演员表.
I might even remove the outer cast, as well.
我们在这里了解到，我们应该选择类型以匹配我们现在实际拥有的 的精度和小数位数，而不是预期的结果.只求大精度数字是没有意义的，因为SQL Server会在算术运算期间对那些类型进行突变，以试图避免溢出.
We learn here we should choose types to match the precision and scale we actually have right now, rather than the expected result. It doesn't make sense to just go for big precision numbers, because SQL Server will mutate those types during arithmetic operations to try to avoid overflows.
- Precision, Scale, and Length using
Sql_Variant_Property() - Operator Precedence
- Data type precedence
这篇关于为什么在SQL中为199.96-0 = 200?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
