英文:
Money data type issue
问题
关于 SQL Server Money 数据类型的问题：
脚本1会导致错误：
Msg 8115, Level 16, State 2, Line 14
Arithmetic overflow error converting expression to data type money.
我尝试将内容移动到 Excel，并获得第二个脚本的金额，它可以正常运行。对于这种行为是否有解释，我不确定。
英文:
Just wondering about SQL Server Money data type:
------- SCRIPT 1
declare
@test table (
Seg varchar(1),
amt money
)
insert @test
select 'a', -427614103835526.00 union all
select 'a', -219289921481827.00 union all
select 'a', -169175063383313.00 union all
select 'a', -151094936902350.00 union all
select 'a', 522155575758567.00 union all
select 'a', 395662864132351.00 union all
select 'a', 187076841800608.00
select SUM(amt) from @test
------ SCRIPT 2
declare
@amt money
set @amt = 137721256088510.00
As you see, the script 1 will failed
> Msg 8115, Level 16, State 2, Line 14
> Arithmetic overflow error converting expression to data type money."
I tried to move the content to Excel and got the amount of the 2nd Script and it runs well. Is there any kind of explanation on this behavioral?
答案1
得分: 2
To avoid overflows and to maintain a predictable ~15 significant digits in calculations, convert to float and then back.
避免溢出并保持计算中的约15个有效数字的可预测性，先转换为浮点数，然后再转回。
select cast(SUM(cast(amt as float)) as money) from @test
选择将amt作为浮点数进行转换后的总和，然后将其转换为货币类型。
This is not just for MONEY. Floating point math is more predictable and useful than the rules for decimal precision and scale in calculations with DECIMAL, which sacrifice precision to allow for potentially large results.
这不仅适用于货币类型。浮点数运算比使用DECIMAL进行计算时的精度和小数位数规则更加可预测和有用，后者为了可能的大型结果而牺牲了精度。
EG
例如
declare @a decimal(38,8) = 2.0001
select @a*@a d, cast(@a as float)*cast(@a as float) f
声明一个decimal(38,8)的变量@a，并计算@a的平方d，以及将@a转换为浮点数后计算的平方f。
outputs
输出结果
d f
4.000400 4.00040001
(1 row affected)
因为两个decimal(38,8)值的乘积是decimal(38,6)。
select SQL_VARIANT_PROPERTY(@a*@a,'Precision') Precision, SQL_VARIANT_PROPERTY(@a*@a,'Scale') Scale
选择查询@a*@a的Precision属性和Scale属性。
outputs
输出结果
Precision Scale
38 6
英文:
To avoid overflows and to maintain a predictable ~15 significant digits in calculations, convert to float and then back.
select cast(SUM(cast(amt as float)) as money) from @test
This is not just for MONEY. Floating point math is more predictable and useful than the rules for decimal precision and scale in calculations with DECIMAL, which sacrifice precision to allow for potentially large results.
EG
declare @a decimal(38,8) = 2.0001
select @a*@a d, cast(@a as float)*cast(@a as float) f
outputs
d f
--------------------------------------- ----------------------
4.000400 4.00040001
(1 row affected)
Because the product of two decimal(38,8) values is decimal(38,6).
select SQL_VARIANT_PROPERTY(@a*@a,'Precision') Precision, SQL_VARIANT_PROPERTY(@a*@a,'Scale') Scale
outputs
Precision Scale
--------------------- -----------
38 6
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论