gpt4 book ai didi

SQL Server 2005 : Scale of numeric expression changes when adding SUM()

转载 作者:行者123 更新时间:2023-12-03 02:33:00 27 4
gpt4 key购买 nike

我有一个名为“Jrl”的表,包含三列:

code  AS varchar(15)
total AS numeric(13,2)
rem AS numeric(13,4)

为了便于论证,我们假设表中只有一行的值为“001”、400.00 和 52.1745。

考虑以下查询:

SELECT code, total - rem
FROM Jrl

它返回一行包含“001”和347.8255。这是正确的。

如果我按如下方式更改查询(这实际上是我的代码中需要的查询):

SELECT code, SUM(total) - SUM(rem)
FROM Jrl
GROUP BY code

它返回一行包含“001”和347.83(即比例为 2 而不是 4)。

现在根据 http://msdn.microsoft.com/en-us/library/ms190476%28v=sql.90%29.aspx 的文档,数值表达式(减法)的类型应该是 numeric(16,4),但显然不是。 (我在 SQL Server 2005 和 2008 R2 上得到了相同的行为。)

有人可以告诉我那里发生了什么吗?

顺便说一句。我确实找到了解决方法,但我不喜欢它,这就是我发布这个问题的原因。解决方法是添加显式强制转换:

SELECT code, CAST(SUM(total) AS numeric(13,4)) - SUM(rem)
FROM Jrl
GROUP BY code

最佳答案

1) 请运行此脚本并阅读我的评论。

2)希望这个答案对您有所帮助。

3) SUM()-SUM() 的精度为 2,因为您首先选择 sum(SUM(total)SUM(rem)),然后减去 (SUM(total) - SUM(rem))。

4)我的建议是使用SELECT t.code, SUM(t.total - t.rem) AS diff ...(先减去,然后求和)。

5)你可以阅读我对这个问题的回答SQL Numeric data type truncating value? :

DECLARE @Test TABLE(
code varchar(15),
total numeric(13,2),
rem numeric(13,4)
);

INSERT @Test (code, total, rem)
VALUES ('001', 11.78, 5.6789);

--Test [1]
SELECT dt.*,
SQL_VARIANT_PROPERTY(dt.diff, 'BaseType') AS diff_BaseType,
SQL_VARIANT_PROPERTY(dt.diff, 'Precision') AS diff_Precision,
SQL_VARIANT_PROPERTY(dt.diff, 'Scale') AS diff_Scale
FROM
(
SELECT t.code, t.total - t.rem AS diff
FROM @Test t
) dt;

/*
Operation: e1 - e2
Result precision: max(s1, s2) + max(p1-s1, p2-s2) + 1 = max(2,4) + max(13-2, 13-4) + 1 = 4 + 11 + 1 = 16
Result scale: max(s1, s2) = max(2, 4) = 4
*/

--Test [2]
SELECT dt.*,
SQL_VARIANT_PROPERTY(dt.diff, 'BaseType') AS diff_BaseType,
SQL_VARIANT_PROPERTY(dt.diff, 'Precision') AS diff_Precision,
SQL_VARIANT_PROPERTY(dt.diff, 'Scale') AS diff_Scale
FROM
(
SELECT t.code, SUM(t.total - t.rem) AS diff
FROM @Test t
GROUP BY t.code
) dt;

/*
Operation: SUM(e1 - e2)
Result precision: 38--For SUM function, I think (it's just a hipotese), SQL Server choose the maximum precision to prevent the overflow error
Argument:
DECLARE @t TABLE (Col NUMERIC(2,1)); INSERT @t VALUES (1);
SELECT SQL_VARIANT_PROPERTY(SUM(t.Col), 'Precision') FROM @t t;
Result: precision = 38 (maximum DECIMAL/NUMERIC precision)
Result scale: the same scale as (e1-e2)= 4 (please see Test [1])
*/

--Test [3]
SELECT dt.*,
SQL_VARIANT_PROPERTY(dt.SUM_total, 'BaseType') AS SUM_total_BaseType,
SQL_VARIANT_PROPERTY(dt.SUM_total, 'Precision') AS SUM_total_Precision,
SQL_VARIANT_PROPERTY(dt.SUM_total, 'Scale') AS SUM_total_Scale,

SQL_VARIANT_PROPERTY(dt.SUM_rem, 'BaseType') AS SUM_rem_BaseType,
SQL_VARIANT_PROPERTY(dt.SUM_rem, 'Precision') AS SUM_rem_Precision,
SQL_VARIANT_PROPERTY(dt.SUM_rem, 'Scale') AS SUM_rem_Scale,

SQL_VARIANT_PROPERTY(dt.diff, 'BaseType') AS diff_BaseType,
SQL_VARIANT_PROPERTY(dt.diff, 'Precision') AS diff_Precision,
SQL_VARIANT_PROPERTY(dt.diff, 'Scale') AS diff_Scale
FROM
(
SELECT t.code,
SUM(t.total) AS SUM_total, SUM(t.rem) AS SUM_rem, SUM(t.total) - SUM(t.rem) AS diff
FROM @Test t
GROUP BY t.code
) dt;

/*
Operation: SUM(total) (<> e1 + e2 + ...)
Result precision: 38--I think SQL Server choose the maximum precision to prevent the overflow error
Result scale: the same precision as total= 2
*/


/*
Operation: SUM(rem) (<> e1 + e2 + ...)
Result precision: 38--I think SQL Server choose the maximum precision to prevent the overflow error
Result scale: the same precision as rem= 4
*/

/*
Operation: SUM(total) - SUM(rem) = e1 - e2
Result precision: max(s1, s2) + max(p1-s1, p2-s2) + 1 = max(2,4) + max(38-2, 38-4) + 1 = 4 + 36 + 1 = 41
but max. precision is 38 so result precision = 38

Calculated result scale: max(s1, s2) = 4
but because the real precision for result (41) is greater than maximum precision (38)
SQL Server choose to decrease the precision of the result to 2 (please see Test [3] - diff_Scale).
In this case (the real precision for result is greater than maximum precision) I think the
expression for result's precision is max(s1, s2) - (real precision - maximum precision) + 1 = 4 - (41 - 38) + 1 = 4 - 3 + 1 = 2
For example you could try to modify the definition of total column to `total numeric(13,1)`
and you will see that the precision for SUM(total) - SUM(rem) becomes 4 - 4(4+37+1=42) + 1 = 1
*/

结果:

--Test [1] SELECT t.code, t.total - t.rem AS diff
code diff diff_BaseType diff_Precision diff_Scale
---- ------ -------------- -------------- ----------
001 6.1011 numeric 16 4

--Test [2] SELECT t.code, SUM(t.total - t.rem) AS diff
code diff diff_BaseType diff_Precision diff_Scale
---- ------ ------------- -------------- ----------
001 6.1011 numeric 38 4

--Test [3] SELECT t.code, ..., SUM(t.total) - SUM(t.rem) AS diff
code SUM_total SUM_rem diff SUM_total_BaseType SUM_total_Precision SUM_total_Scale SUM_rem_BaseType SUM_rem_Precision SUM_rem_Scale diff_BaseType diff_Precision diff_Scale
---- --------- ------- ---- ------------------ ------------------- --------------- ---------------- ------------------------------- ------------- -------------- ----------
001 11.78 5.6789 6.10 numeric 38 2 numeric 38 4 numeric 38 2

关于SQL Server 2005 : Scale of numeric expression changes when adding SUM(),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10395816/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com