为什么在SQL中199.96 - 0 = 200？

Question

为什么在SQL中199.96 - 0 = 200？

sql-servert-sqlprecisionsql-server-2016sqldatatypes

83

我有一些客户收到了奇怪的账单。我已经成功地确定了核心问题：

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....

有没有人知道这里到底发生了什么？我的意思是，这肯定与十进制数据类型有关，但我真的无法理解...

对于数字文字的数据类型有很多混淆，所以我决定展示实际行：

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)类型的操作数的操作结果在应用到外部上下文之前进行了显式转换。

尽管如此，结果仍然是200.00。

我现在创建了一个精简的样例，您可以在计算机上执行。

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

现在我有一些东西…

-- 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)))

到底是怎么回事，floor函数本来就应该返回整数，为什么会出现这种情况？:-D

我觉得我现在已经把问题精简到了最核心的部分 :-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))
)

- Silverdust

4

199.96 - 0 不等于200。所有的类型转换、取整和浮点数的隐式转换都会损失精度。 - Panagiotis Kanavos

1

只有当它来自表格时，@Silverdust 才是一个有效的值。如果它在表达式中作为字面量出现，则很可能是一个 float 类型。 - Panagiotis Kanavos

1

哦... Floor() 不会返回一个 int。它返回与原始表达式相同的类型，但去除了小数部分。对于其余部分，IIF() 函数的结果是具有最高优先级的类型 (https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql)。因此，在第二个示例中，您将转换为 int，较高的优先级是简单的 numeric(19,4) 转换。 - Joel Coehoorn

1

@Silverdust - MS SQL Server和Sybase都将它们的语言称为T-SQL，因为它们曾经在同一个基本产品上共同合作。有关详细信息，请参见此MS博客文章。 - RDFozz

2

我对MS SQL不是太熟悉，但我必须承认，看到所有的数据类型转换操作等等很快就引起了我的注意。所以我必须分享这个链接，因为没有人应该使用浮点类型来处理货币。链接地址：https://dev59.com/inRB5IYBdhLWcg3wkIBN - code_dredd

显示剩余9条评论

2个回答

20

请注意以下语句涉及到的数据类型：

SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))))

NUMERIC(19, 4) * NUMERIC(19, 4) 等于 NUMERIC(38, 7) (见下方)
- FLOOR(NUMERIC(38, 7)) 等于 NUMERIC(38, 0) (见下方)
0.0 等于 NUMERIC(1, 1)
- NUMERIC(1, 1) * NUMERIC(38, 0) 等于 NUMERIC(38, 1)
199.96 等于 NUMERIC(5, 2)
- NUMERIC(5, 2) - NUMERIC(38, 1) 等于 NUMERIC(38, 1) (见下方)

这就解释了为什么你最终得到的是 200.0（小数点后有一位数字，不是零），而不是 199.96。

注：

FLOOR 返回不大于指定数值表达式的最大整数，其结果与输入类型相同。它对于 INT 返回 INT，对于 FLOAT 返回 FLOAT，对于 NUMERIC(x, y) 返回 NUMERIC(x, 0)。

根据算法：

Operation | Result precision                    | Result scale*
e1 * e2   | p1 + p2 + 1                         | s1 + s2
e1 - e2   | max(s1, s2) + max(p1-s1, p2-s2) + 1 | max(s1, s2)

*结果的精度和位数最大为38。当结果精度大于38时，它将被减少到38，并且相应的刻度也会尝试降低，以防止结果的整数部分被截断。

// https://learn.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-2017

function numericTest_mul(p1, s1, p2, s2) {
  // e1 * e2
  var precision = p1 + p2 + 1;
  var scale = s1 + s2;

  // see notes in the linked article about multiplication operations
  var newscale;
  if (precision - scale < 32) {
    newscale = Math.min(scale, 38 - (precision - scale));
  } else if (scale < 6 && precision - scale > 32) {
    newscale = scale;
  } else if (scale > 6 && precision - scale > 32) {
    newscale = 6;
  }

  console.log("NUMERIC(%d, %d) * NUMERIC(%d, %d) yields NUMERIC(%d, %d) clamped to NUMERIC(%d, %d)", p1, s1, p2, s2, precision, scale, Math.min(precision, 38), newscale);
}

function numericTest_add(p1, s1, p2, s2) {
  // e1 + e2
  var precision = Math.max(s1, s2) + Math.max(p1 - s1, p2 - s2) + 1;
  var scale = Math.max(s1, s2);

  // see notes in the linked article about addition operations
  var newscale;
  if (Math.max(p1 - s1, p2 - s2) > Math.min(38, precision) - scale) {
    newscale = Math.min(precision, 38) - Math.max(p1 - s1, p2 - s2);
  } else {
    newscale = scale;
  }

  console.log("NUMERIC(%d, %d) + NUMERIC(%d, %d) yields NUMERIC(%d, %d) clamped to NUMERIC(%d, %d)", p1, s1, p2, s2, precision, scale, Math.min(precision, 38), newscale);
}

function numericTest_union(p1, s1, p2, s2) {
  // e1 UNION e2
  var precision = Math.max(s1, s2) + Math.max(p1 - s1, p2 - s2);
  var scale = Math.max(s1, s2);

  // my idea of how newscale should be calculated, not official
  var newscale;
  if (precision > 38) {
    newscale = scale - (precision - 38);
  } else {
    newscale = scale;
  }

  console.log("NUMERIC(%d, %d) + NUMERIC(%d, %d) yields NUMERIC(%d, %d) clamped to NUMERIC(%d, %d)", p1, s1, p2, s2, precision, scale, Math.min(precision, 38), newscale);
}

/*
 * first example in question
 */

// CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))
numericTest_mul(19, 4, 19, 4);

// 0.0 * FLOOR(...)
numericTest_mul(1, 1, 38, 0);

// 199.96 * ...
numericTest_add(5, 2, 38, 1);

/*
 * IIF examples in question
 * the logic used to determine result data type of IIF / CASE statement
 * is same as the logic used inside UNION operations
 */

// FLOOR(DECIMAL(38, 7)) UNION CAST(1999.96 AS DECIMAL(19, 4)))
numericTest_union(38, 0, 19, 4);

// CAST(1.0 AS DECIMAL (36, 0)) UNION CAST(1.96 AS DECIMAL(19, 4))
numericTest_union(36, 0, 19, 4);

// CAST(1.0 AS DECIMAL (37, 0)) UNION CAST(1.96 AS DECIMAL(19, 4))
numericTest_union(37, 0, 19, 4);

// CAST(1.0 AS DECIMAL (38, 0)) UNION CAST(1.96 AS DECIMAL(19, 4))
numericTest_union(38, 0, 19, 4);

- Salman A

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Stanislav Kundii · Accepted Answer

我需要先解开一些包装，以便看清楚发生了什么：

SELECT 199.96 - 
    (
        0.0 * 
        FLOOR(
            CAST(1.0 AS DECIMAL(19, 4)) * 
            CAST(199.96 AS DECIMAL(19, 4))
        )
    )

现在让我们看看SQL Server在减法运算的每个方面使用了哪些类型：

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')

结果:

数值型 5   2
数值型 38  1

所以 199.96 是 numeric(5,2)，而更长的 Floor(Cast(etc)) 是 numeric(38,1)。

减法操作（即 e1 - e2）的结果精度和标度的规则如下：

精度：max(s1, s2) + max(p1-s1, p2-s2) + 1
标度：max(s1, s2)

它的计算过程如下：

精度：max(1,2) + max(38-1, 5-2) + 1 => 2 + 37 + 1 => 40
标度：max(1,2) => 2

您还可以使用规则链接来查找首次出现 numeric(38,1) 的位置（提示：您乘以两个精度为19的值）。

但是：

结果精度和刻度的绝对最大值为38。当结果精度大于38时，会将其减少到38，并尝试减少相应的刻度以防止结果的整数部分被截断。在某些情况下，例如乘法或除法，刻度因子不会被减少以保持小数精度，尽管可能会引发溢出错误。哎呀，精度是40。我们必须将其降低，由于降低精度应始终削减最不重要的数字，这意味着也要减少比例。表达式的最终结果类型将为numeric（38,0），对于199.96四舍五入为200。您可以通过将CAST（）操作从大表达式内部移动和合并到整个表达式结果周围的一个CAST（）中来修复此问题。所以这个：

SELECT 199.96 - 
    (
        0.0 * 
        FLOOR(
            CAST(1.0 AS DECIMAL(19, 4)) * 
            CAST(199.96 AS DECIMAL(19, 4))
        )
    )

Becomes:

SELECT CAST( 199.96 - ( 0.0 * FLOOR(1.0 * 199.96) ) AS decimial(19,4))

我甚至可能会去掉外部转换。

我们在这里学到，应该选择与我们实际拥有的精度和比例相匹配的类型，而不是预期结果。仅仅选择大精度数字是没有意义的，因为SQL Server在算术运算期间会改变这些类型，以尝试避免溢出。

为什么在SQL中199.96 - 0 = 200？

更多信息：