隐式转换和舍入 [英] Implicit conversions and rounding

问题描述

刚遇到一个有趣的:

declare @test as int
set @test = 47

select @test * 4.333

返回 203.651

declare @test as int
set @test = 47

declare @out as int
set @out = (select @test * 4.333)

select @out

返回 203

declare @test as int
set @test = 47

declare @out as int
set @out = round((select @test * 4.333),0)

select @out

返回 204

现在我知道为什么它会这样做.这是因为有一个从十进制到 int 的隐式转换，因此小数位需要被砍掉(因此是 203)，而如果我在隐式转换之前四舍五入，我得到 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.

我的问题是为什么当 SQL Server 进行隐式转换时它不是也四舍五入?我知道如果我有一个很大的数字，并且需要将它存储在一个小地方，我的第一件事就是d 是将其四舍五入以尽可能接近原始数字.

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.

推荐答案

这让我读了起来，答案似乎明显不令人满意，我能找到的最早的 SQL 参考(ANSI 92 可用 此处)在4.4.1 数字特征 声明

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

每当一个精确或近似的数值被分配给一个表示精确数值的数据项或参数，其价值的近似值，保留领先的重要四舍五入或截断后的数字在数据中表示目标的类型.该值被转换为具有精度和目标的规模.截断或舍入的选择是实现定义的.

Whenever an exact or approximate numeric value is assigned to a data item or parameter representing an exact numeric value, an approximation of its value that preserves leading significant digits after rounding or truncating is represented in the data type of the target. The value is converted to have the precision and scale of the target. The choice of whether to truncate or round is implementation-defined.

这由微软决定，他们选择为 T-SQL 实现这两个中的哪一个，我假设为了简单起见，他们选择了截断.从 维基百科关于四舍五入的文章 看来，这在那天.

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.

有趣的是，根据我找到的文档，只有转换为整数会导致截断，其他会导致舍入.尽管出于某种奇怪的原因，从 money 到 integer 的转换似乎与趋势相反，因为它允许四舍五入.

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 to integer appears to buck the trend as it's allowed to round.

From     To       Behaviour

numeric  numeric  Round

numeric  int      Truncate

numeric  money    Round

money    int      Round

money    numeric  Round

float    int      Truncate

float    numeric  Round

float    datetime Round

datetime int      Round

来自此处.

这篇关于隐式转换和舍入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！