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.
我读了T-SQL的所有舍入函数,如Round,Floor和Ceil,但它们都没有正确地向下舍入十进制数。
I have 2 questions:
我有两个问题:
- How to round down a decimal number (
3.69
==>3.5
)? - 如何舍入十进制数(3.69 ==> 3.5)?
- How to round up the last 3 digits of an integer (e.g.
142600
==>143000
)? - 如何舍入整数的最后3位数(例如142600 ==> 143000)?
3 个解决方案
#1
6
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.
1)选择CAST(FLOOR(2 * 3.69)/ 2 AS十进制(2,1))处理第一种情况 - 礼貌地回答SQL Server论坛上的类似问题,我对其进行了调整并快速检查。
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:
请注意,如果要舍入到最接近的0.5的数字可能更大(例如333.69 => 333.5),请确保在转换时指定更多小数精度(例如,选择CAST(FLOOR(2 * 3.69)/ 2 AS十进制(10) ,1))),或者您可能会收到溢出错误:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
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.
额外的精度不会影响底线结果(即选择CAST(FLOOR(2 * 3.69)/ 2 AS十进制(10,1))并选择CAST(FLOOR(2 * 3.69)/ 2 AS十进制(2,1) )两者产量3.5);但如果你要四舍五入的数字总是更小,那就太浪费了。
Online references with examples are available for T-SQL FLOOR
, CAST
, and decimal
to help.
带有示例的在线参考可用于T-SQL FLOOR,CAST和十进制帮助。
2) select ROUND(142600, -3)
handles the second case.
2)选择ROUND(142600,-3)处理第二种情况。
A similar online reference is available for T-SQL ROUND
.
类似的在线参考可用于T-SQL ROUND。
#2
1
As per @J0e3gan 's anwser, Sql Server's Round allows rounding to the nearest powers of 10 using the length
parameter, where length is 10^(-length)
, e.g.
根据@ J0e3gan的anwser,Sql Server的Round允许使用length参数舍入到最接近的10的幂,其中length是10 ^( - length),例如,
length = 0 : 10 ^ 0 = nearest 1
length = 3 : 10 ^ -3 = nearest .001
length = -3 : 10 ^ 3 = nearest 1000
etc
等等
However, in general, with a simple 1-based rounding function - e.g. (Sql Round with Length=0) to round to an arbitrary value of "nearest N" - with the formula:
然而,通常,使用简单的基于1的舍入函数 - 例如, (Sql Round with Length = 0)舍入到“最接近的N”的任意值 - 使用以下公式:
round(X / N) * N
e.g. nearest 100
例如最近的100
select round(12345 / 100.0, 0) * 100.0 -- 12300
select round(-9876 / 100.0, 0) * 100.0 -- -9900
select round(-9849 / 100.0, 0) * 100.0 -- -9800
... Nearest 0.5
......最近的0.5
select round(5.123 / 0.5, 0) * 0.5 -- 5.000
select round(6.499 / 0.5, 0) * 0.5 -- 6.500
select round(-4.499 / 0.5, 0) * 0.5 -- -4.50
... Nearest 0.02
......最近0.02
select round(5.123 / .02, 0) * .02 -- 5.12
select round(-9.871 / .02, 0) * .02 -- -9.88
etc
等等
Remember that the type used for the divisors must be numeric / decimal or float.
请记住,用于除数的类型必须是数字/小数或浮点数。
#3
0
The Oracle/PLSQL FLOOR
function returns the largest integer value that is equal to or less than a number. eg:
Oracle / PLSQL FLOOR函数返回等于或小于数字的最大整数值。例如:
FLOOR(7.9)
Result: 7
FLOOR(30.29)
Result: 30
FLOOR(-7.9)
Result: -8
#1
6
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.
1)选择CAST(FLOOR(2 * 3.69)/ 2 AS十进制(2,1))处理第一种情况 - 礼貌地回答SQL Server论坛上的类似问题,我对其进行了调整并快速检查。
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:
请注意,如果要舍入到最接近的0.5的数字可能更大(例如333.69 => 333.5),请确保在转换时指定更多小数精度(例如,选择CAST(FLOOR(2 * 3.69)/ 2 AS十进制(10) ,1))),或者您可能会收到溢出错误:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
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.
额外的精度不会影响底线结果(即选择CAST(FLOOR(2 * 3.69)/ 2 AS十进制(10,1))并选择CAST(FLOOR(2 * 3.69)/ 2 AS十进制(2,1) )两者产量3.5);但如果你要四舍五入的数字总是更小,那就太浪费了。
Online references with examples are available for T-SQL FLOOR
, CAST
, and decimal
to help.
带有示例的在线参考可用于T-SQL FLOOR,CAST和十进制帮助。
2) select ROUND(142600, -3)
handles the second case.
2)选择ROUND(142600,-3)处理第二种情况。
A similar online reference is available for T-SQL ROUND
.
类似的在线参考可用于T-SQL ROUND。
#2
1
As per @J0e3gan 's anwser, Sql Server's Round allows rounding to the nearest powers of 10 using the length
parameter, where length is 10^(-length)
, e.g.
根据@ J0e3gan的anwser,Sql Server的Round允许使用length参数舍入到最接近的10的幂,其中length是10 ^( - length),例如,
length = 0 : 10 ^ 0 = nearest 1
length = 3 : 10 ^ -3 = nearest .001
length = -3 : 10 ^ 3 = nearest 1000
etc
等等
However, in general, with a simple 1-based rounding function - e.g. (Sql Round with Length=0) to round to an arbitrary value of "nearest N" - with the formula:
然而,通常,使用简单的基于1的舍入函数 - 例如, (Sql Round with Length = 0)舍入到“最接近的N”的任意值 - 使用以下公式:
round(X / N) * N
e.g. nearest 100
例如最近的100
select round(12345 / 100.0, 0) * 100.0 -- 12300
select round(-9876 / 100.0, 0) * 100.0 -- -9900
select round(-9849 / 100.0, 0) * 100.0 -- -9800
... Nearest 0.5
......最近的0.5
select round(5.123 / 0.5, 0) * 0.5 -- 5.000
select round(6.499 / 0.5, 0) * 0.5 -- 6.500
select round(-4.499 / 0.5, 0) * 0.5 -- -4.50
... Nearest 0.02
......最近0.02
select round(5.123 / .02, 0) * .02 -- 5.12
select round(-9.871 / .02, 0) * .02 -- -9.88
etc
等等
Remember that the type used for the divisors must be numeric / decimal or float.
请记住,用于除数的类型必须是数字/小数或浮点数。
#3
0
The Oracle/PLSQL FLOOR
function returns the largest integer value that is equal to or less than a number. eg:
Oracle / PLSQL FLOOR函数返回等于或小于数字的最大整数值。例如:
FLOOR(7.9)
Result: 7
FLOOR(30.29)
Result: 30
FLOOR(-7.9)
Result: -8