ROUND ( 123.456 , 2 , 1 )

当第三个参数!= 0时，它截断而不是舍入。

语法

ROUND ( numeric_expression , length [ ,function ] )

参数

numeric_expression Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. length Is the precision to which numeric_expression is to be rounded. length must be an expression of type tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length. function Is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

我知道这个问题非常古老，但没有人使用子字符串舍入。这是对很长的数字进行四舍五入的优势(SQL server中字符串的限制通常是8000个字符):

SUBSTRING('123.456', 1, CHARINDEX('.', '123.456') + 2)

select round(123.456, 2, 1)

我认为我们可以在Hackerrank中找到更简单的示例解决方案:

问题说明:查询北纬的最大值 (LAT_N)来自小于137.2345的STATION。截断你的答案 小数点后4位。

SELECT TRUNCATE(MAX(LAT_N),4)
FROM STATION
WHERE LAT_N < 137.23453;

上面的解决方案告诉你如何简单地将值限制在4个小数点内。如果你想降低或提高小数后面的数字，只要把4改成你想要的任何数字。

实际上，不管第三个参数是0或1或2，它不会四舍五入你的值。

CAST(ROUND(10.0055,2,0) AS NUMERIC(10,2))

请尝试使用此代码将一个点后的3个十进制值转换为2位小数:

declare @val decimal (8, 2)
select @val = 123.456
select @val =  @val

select @val

输出值为123.46