为什么 Sql Server 2000 上的 TSQL 舍入小数不一致? [英] Why does TSQL on Sql Server 2000 round decimals inconsistently?
问题描述
我正在尝试计算美元金额的折扣值.在 50% 时，有时您会得到半美分，我需要将其四舍五入到最接近的美分.
I'm trying to calculate percent off values for dollar amounts. At 50%, sometimes you get half a penny, and I need to round it to the nearest cent.
在Sql中，我的计算如下:
In Sql, my calculation is as follows:
round(retail * 0.5, 2, 0)
如果我采用以下值，我会得到不同的结果:
If I take the following values I get different results:
- 4.39
- 2.49
我得到不圆角:
- 4.39 --> 2.195
- 2.49 --> 1.245
四舍五入后:
- 2.195 --> 2.19
- 1.245 --> 1.25
有人告诉我这是银行家的舍入"的一种形式，但似乎影响舍入方向的值是整数值.
I'm told this is a form of "banker's rounding", but it seems like the value that is affecting the rounding direction is the integer value.
我的问题是我希望最高值四舍五入到 2.20.如果这确实是银行家四舍五入，并且受此处整数值的影响，是否有人有防止这种行为的示例.如果不是银行家四舍五入，有人可以提供解释并提供可能的解决方案来获得正常的四舍五入行为吗?
My problem is that I expect the top value to round to 2.20. If this is indeed bankers rounding, and it is affected by the integer value here, does anyone have an example for preventing this behavior. If it is not bankers rounding, can someone please provide an explanation and potentially a solution to just get normal rounding behavior?
提前致谢！
推荐答案
retail 的数据类型是什么?我无法在 SQL 2000 上对此进行测试，但在 SQL 2008 R2 上，如果我使用 float，我会看到类似的结果.
What is the data type of retail? I can't test this on SQL 2000, but on SQL 2008 R2, I see similar results if I use float.
float: 4.39 --> 2.195, rounds to 2.19
2.49 --> 1.245, rounds to 1.25
money: 4.39 --> 2.195, rounds to 2.20
2.49 --> 1.245, rounds to 1.25
decimal(5,2): 4.39 --> 2.195, rounds to 2.20
2.49 --> 1.245, rounds to 1.25
<小时>
要显示由 float 创建的近似值，您可以强制转换为 decimal(20,19) 并且很清楚为什么它会以这种方式四舍五入:
To show the approximation created by float, you can cast to decimal(20,19) and it becomes clear why it's rounding this way:
4.39 * 0.5 --> 2.1949999999999999000
2.49 * 0.5 --> 1.2450000000000001000
这篇关于为什么 Sql Server 2000 上的 TSQL 舍入小数不一致?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
