在 SQL Server 2008 中四舍五入十进制数

本文介绍了在 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.