隐式转换和舍入
刚刚遇到一个有趣的:
declare @test as int
set @test = 47
select @test * 4.333
returns 203.651
declare @test as int
set @test = 47
declare @out as int
set @out = (select @test * 4.333)
select @out
returns 203
declare @test as int
set @test = 47
declare @out as int
set @out = round((select @test * 4.333),0)
select @out
returns 204
现在我知道为什么它会这样做。这是因为存在从小数到整数的隐式转换,因此需要截掉小数位(因此为 203),而如果我在隐式转换之前四舍五入,则会得到 204。
我的问题是为什么当 SQL Server 执行隐式转换时转换不是也四舍五入吗? 我知道如果我有一个很大的数字,并且需要将其存储在一个小地方,我要做的第一件事就是对其进行四舍五入,以便尽可能接近原始值数量尽可能。
对我来说这似乎并不直观。
Just come across an interesting one:
declare @test as int
set @test = 47
select @test * 4.333
returns 203.651
declare @test as int
set @test = 47
declare @out as int
set @out = (select @test * 4.333)
select @out
returns 203
declare @test as int
set @test = 47
declare @out as int
set @out = round((select @test * 4.333),0)
select @out
returns 204
Now I know why it does this. Its because there is an implicit conversion from decimal to int, therefore the decimal places need chopped off (hence 203), whereas if I round prior to the implicit conversion I get 204.
My question is why when SQL Server does an implicit conversion is it not also rounding? I know if I had a big number, and it needed stored in a small place, the first thing I'd do would be to round it so as to be as close to the original number as possible.
It just doesn't seem intuitive to me.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
这让我开始阅读,答案似乎显然不能令人满意,我能找到的最早的 SQL 参考(ANSI 92 可用 此处)在 4.4.1 数字特征 节中指出
这让 Microsoft 选择为 T-SQL 实现两者中的哪一个,我假设为了简单起见,他们选择了截断。从 关于舍入的维基百科文章来看,这似乎并不是一个不常见的决定那天。
有趣的是,根据我发现的文档,只有转换为整数才会导致截断,其他转换会导致四舍五入。尽管出于某种奇怪的原因,从货币到整数的转换似乎与趋势相反,因为它允许四舍五入。
表来自 此处。
This got me reading and the answer seems to be distinctly unsatisfying, The earliest SQL reference I've been able to find (ANSI 92 available here) in section 4.4.1 Characteristics of numbers states that
Which leaves it up to Microsoft which of the two they chose to implement for T-SQL and I assume for the sake of simplicity they chose truncation. From the wikipedia article on rounding it seems that this wasn't an uncommon decision back in the day.
It's interesting to note that, according to the documentation I found, only conversions to integers cause truncation, the others cause rounding. Although for some bizarre reason the conversion from
money
tointeger
appears to buck the trend as it's allowed to round.Table from here.