在 MySQL 中如何四舍五入到最接近的整数？

如何四舍五入到 MySQL 中最接近的整数？

例子: 12345.7344 rounds to 12345

Mysql 的 round()函数四舍五入。

我不知道这些数值和小数位有多长，可能是10位4位小数，也可能是2位7位小数。

208984 次浏览

小开

最佳答案

Use FLOOR:

SELECT FLOOR(your_field) FROM your_table

小开

SELECT FLOOR(12345.7344);

SQLFiddle Demo

小开

SUBSTR will be better than FLOOR in some cases because FLOOR has a "bug" as follow:

SELECT 25 * 9.54 + 0.5 -> 239.00


SELECT FLOOR(25 * 9.54 + 0.5) -> 238  (oops!)


SELECT SUBSTR((25*9.54+0.5),1,LOCATE('.',(25*9.54+0.5)) - 1) -> 239

小开

Use FLOOR().

It will to round your decimal to the lower integer. Examples:

SELECT FLOOR(1.9) /* return 1 */
SELECT FLOOR(1.1) /* return 1 */

Other useful rounding

If you want to round your decimal to the nearest integer, use ROUND(). Examples:

SELECT ROUND(1.9) /* return 2 */
SELECT ROUND(1.1) /* return 1 */

If you want to round your decimal to the upper integer, use CEILING(). Examples:

SELECT CEILING(1.9) /* return 2 */
SELECT CEILING(1.1) /* return 2 */

小开

if you need decimals can use this

DECLARE @Num NUMERIC(18, 7) = 19.1471985
SELECT FLOOR(@Num * 10000) / 10000

Output: 19.147100 Clear: 985 Add: 00

OR use this:

SELECT SUBSTRING(CONVERT(VARCHAR, @Num), 1, CHARINDEX('.', @Num) + 4)

Output: 19.1471 Clear: 985

小开

It can be done in the following two ways:

select floor(desired_field_value) from table
select round(desired_field_value-0.5) from table

The 2nd-way explanation: Assume 12345.7344 integer. So, 12345.7344 - 0.5 = 12345.2344 and rounding off the result will be 12345.

小开

Both Query is used for round down the nearest integer in MySQL

SELECT FLOOR(445.6) ;
SELECT NULL(222.456);

小开

The FLOOR() function will return the largest integer value that is smaller than or equal to a number.

example :
SELECT FLOOR(columnName) FROM tableName;