在 SQL Server 2008 中四舍五入十进制数 [英] Rounding down decimal numbers in SQL Server 2008
问题描述
我阅读了 T-SQL 的所有舍入函数，例如 Round、Floor 和 Ceil，但它们都没有向下舍入十进制数对我来说是正确的.
I read all rounding functions of T-SQL like Round, Floor, and Ceil, but none of them has rounded down decimal numbers correctly for me.
我有两个问题:
- 如何舍入十进制数 (
3.69==>3.5)? - 如何舍入整数的最后 3 位数字(例如
142600==>143000)?
- How to round down a decimal number (
3.69==>3.5)? - How to round up the last 3 digits of an integer (e.g.
142600==>143000)?
推荐答案
1) select CAST(FLOOR(2 * 3.69)/2 AS decimal(2, 1)) 处理第一种情况 - 由 对 SQL Server 论坛上类似问题的回答提供，我适应并快速检查.
1) select CAST(FLOOR(2 * 3.69) / 2 AS decimal(2, 1)) handles the first case - courtesy of an answer to a similar question on SQL Server Forums, which I adapted and quickly checked.
请注意，如果您四舍五入到最接近的 0.5 的数字可能更大(例如 333.69 => 333.5)，请务必转换时指定更多 decimal 精度(例如 select CAST(FLOOR(2 * 3.69)/2 AS decimal(10, 1)))，否则可能会溢出错误:
Note that if the numbers you are rounding to the nearest 0.5 could be bigger (e.g. 333.69 => 333.5), be sure to specify more decimal precision when you cast (e.g. select CAST(FLOOR(2 * 3.69) / 2 AS decimal(10, 1))), or you could get an overflow error:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
额外的精度不会影响底线结果(即 select CAST(FLOOR(2 * 3.69)/2 AS decimal(10, 1)) 和 select CAST(FLOOR(2 * 3.69)/2 AS 十进制(2, 1)) 都产生 3.5);但如果你四舍五入的数字总是更小，那就太浪费了.
Extra precision will not affect the bottom-line result (i.e. select CAST(FLOOR(2 * 3.69) / 2 AS decimal(10, 1)) and select CAST(FLOOR(2 * 3.69) / 2 AS decimal(2, 1)) both yield 3.5); but it is wasteful if the numbers you are rounding will always be smaller.
提供 T-SQL 的在线参考资料楼层, CAST 和 十进制 提供帮助.
Online references with examples are available for T-SQL FLOOR, CAST, and decimal to help.
2) select ROUND(142600, -3) 处理第二种情况.
T-SQL 有类似的在线参考圆形.
A similar online reference is available for T-SQL ROUND.
这篇关于在 SQL Server 2008 中四舍五入十进制数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
