SQL Server round（）(SQL Server round())

SQL Server round（）(SQL Server round())

我想舍入一个选择的值

例如：

select width from Home

width的结果是“3.999999999999999”

我想成为“3.99”或“3.9”

I want to round value of one select

For example:

select width from Home

The result of width is "3.999999999999999"

I want to be "3.99" or "3.9"

最满意答案

如果你只需要两个十进制值，你可以乘以100，将结果除以100（这是必要的，因为floor只能将一个数字加到一个整数）：

select floor(width * 100) / 100 from Home

这是步骤

3.99999999 * 100 = 399.999999 --- Multiply by 100 floor(399.999999) = 399 --- floor 399 / 100 = 3.99 --- Divide by 100

---

也可以使用具有第三参数的不同形式的圆函数 。

当第三个参数与0不同时，结果将被截断而不是舍入

句法

ROUND ( numeric_expression , length [ ,function ] )

参数

numeric_expression是精确数字或近似数值数据类型类别的表达式，但位数据类型除外。

length是numeric_expression的舍入精度。 length必须是tinyint，smallint或int类型的表达式。 当length为正数时，numeric_expression将四舍五入为length指定的小数位数。 当length为负数时，numeric_expression在小数点的左侧舍入，由length指定。

function是要执行的操作类型。 function必须是tinyint，smallint或int。 省略function或值为0（默认值）时，numeric_expression将四舍五入。 如果指定了0以外的值， 则会截断 numeric_expression 。

以下是使用此版本的round选择：

select round(width, 2, 1) from Home

If you need only two decimal values you can multiply by 100, floor the result and divide by 100 (This is necessary because floor works only flooring a number to an integer):

select floor(width * 100) / 100 from Home

Here are the steps

3.99999999 * 100 = 399.999999 --- Multiply by 100 floor(399.999999) = 399 --- floor 399 / 100 = 3.99 --- Divide by 100

---

It is also possible using a different form of round function with a third parameter.

When the third parameter is different from 0 the result is truncated instead of rounded

Syntax

ROUND ( numeric_expression , length [ ,function ] )

Arguments

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.

Here is the select using this version of round:

select round(width, 2, 1) from Home