在使用MySql的sql查询中,有没有方法可以指定是将字段值4.485四舍五入到4.48(半下)还是4.49(半上)?
感谢
即使这个问题已经存在多年了,我也遇到了同样的问题,并找到了不同的解决方案。
这里是:使用条件函数,您可以明确地决定最后一个小数点为5时要四舍五入的方式。例如,如果你想用2位小数将一半四舍五入:
SELECT IF ((TRUNCATE(mynumber*1000,0) mod 5) = 0,CEIL(mynumber*100)/100,ROUND(mynumber,2)) as value FROM mytable
如果您想向下取整一半,可以使用FLOOR函数而不是CEIL;如果您想要不同数量的小数,比如n,您可以更改TRUNCATE调用内的乘数(10^n+1),然后将其传递给ROUND函数。
SELECT IF ((TRUNCATE(mynumber*([10^*n+1*]),0) mod 5) = 0,[CEIL|FLOOR](mynumber*100)/100,ROUND(mynumber,[*n*])) as value FROM mytable
根据MySQL官方文档,无法指定舍入策略。默认情况下,ROUND使用"向上取整"规则来获取精确的数值。但是,我需要一个函数,它的行为与java.math.BigDecimal
和java.math.RoundingMode.HALF_DOWN
模式类似。所以我找到的唯一解决方案就是创建自己的函数。
代码(在MySQL 5.7中测试)
DELIMITER //
DROP FUNCTION IF EXISTS roundHalfDown //
CREATE FUNCTION roundHalfDown (
numberToRound DECIMAL(25,15),
roundingPrecision TINYINT(2)
)
RETURNS DECIMAL(25,15)
BEGIN
DECLARE digitPosition TINYINT (2) UNSIGNED DEFAULT 0;
DECLARE digitToRound TINYINT (2) DEFAULT -1;
DECLARE roundedNumber DECIMAL(20,6) DEFAULT 0;
SET digitPosition = INSTR(numberToRound, '.');
IF (roundingPrecision < 0) THEN
SET digitPosition = digitPosition + roundingPrecision;
ELSE
SET digitPosition = digitPosition + roundingPrecision + 1;
END IF;
IF (digitPosition > 0
AND digitPosition <= CHAR_LENGTH(numberToRound)
) THEN
SET digitToRound = CAST(
SUBSTR(
numberToRound,
digitPosition,
1
) AS UNSIGNED
);
SET digitPosition = digitPosition - 1;
END IF;
IF (digitToRound > -1) THEN
IF (digitToRound > 5) THEN
SET roundedNumber = ROUND(numberToRound, roundingPrecision);
ELSEIF (digitToRound = 5 AND CAST(
SUBSTR(
REPLACE(numberToRound, '.', ''),
digitPosition + 1
) AS UNSIGNED) > 0) THEN
SET roundedNumber = ROUND(numberToRound, roundingPrecision);
ELSE
SET roundedNumber = TRUNCATE(numberToRound, roundingPrecision);
END IF;
ELSEIF (roundingPrecision > 0) THEN
SET roundedNumber = numberToRound;
END IF;
RETURN roundedNumber;
END //
DELIMITER ;
测试用例
SELECT roundHalfDown(1.541, 2); #1.54
SELECT roundHalfDown(1.545, 2); #1.54
SELECT roundHalfDown(1.5451, 2); #1.55
SELECT roundHalfDown(1.54500001, 2); #1.55
SELECT roundHalfDown(1.54499999, 2); #1.54
SELECT roundHalfDown(-1.545, 2); #-1.54
SELECT roundHalfDown(-1.5451, 2); #-1.55
SELECT roundHalfDown(555, 0); #555
SELECT roundHalfDown(1000999, -1); #1001000
SELECT roundHalfDown(1000999, -2); #1001000
SELECT roundHalfDown(1000999, -3); #1001000
SELECT roundHalfDown(1000999, -4); #1000000
我使用了另一个Stack Overflow问题中的这个答案,但针对我的具体情况,我做了一些更改。
Andrea Pegoretti的答案几乎是正确的,但当它是5或0而不是5时,它就会适用。
我的解决方案(四舍五入):
SELECT IF (SUBSTR(TRUNCATE(CEIL(mynumber*1000),0),-1) = 5,FLOOR(mynumber*100)/100,ROUND(mynumber, 2))) AS value FROM mytable
这篇文章的公认答案提供了一个很好的解决方案:MySQL-我怎么能总是四舍五入小数?
即乘以10^n,其中n是要四舍五入的小数点,然后调用ceil
向上四舍五进或floor
向下四舍五出并除以10^n。
您可以使用CEIL(或CEILING)和FLOOR函数向上取整/向下取整。
例如:
CEIL(4.485 * 100) / 100 will return 4.49
FLOOR(4.485 * 100) / 100 will return 4.48
我有同样的问题,我尝试了所有类型的自定义函数来模拟PHP的功能,包括上下舍入,但过了一段时间,我发现MySQL使用float和doubles字段会产生不同的结果。
这是我的测试。
mysql> describe test_redondeo;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| columna_double | double(15,3) | YES | | NULL | |
| columna_float | float(9,3) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
+----------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
在字段类型double中,舍入函数truncate,但在float中舍入。
mysql> select columna_double, columna_float, round ( columna_double, 2) as round_double, round ( columna_float, 2) as round_float from test_redondeo;
+----------------+---------------+--------------+-------------+
| columna_double | columna_float | round_double | round_float |
+----------------+---------------+--------------+-------------+
| 902.025 | 902.025 | 902.02 | 902.03 |
+----------------+---------------+--------------+-------------+
1 row in set (0.00 sec)
若您以任何方式使用round()创建了一个用于舍入数字的自定义函数,请注意字段的类型。
希望这次测试对你有帮助。
根据Mysql文档,
- 对于精确数值,ROUND()使用"四舍五入"规则
我也遇到过这样的情况,我想知道为什么Mysql四舍五入(1)74447到74,4。这是因为它实际上是一个无穷长的小数,是一个近似值的数字,而不是一个精确值的数字。对我来说,解决方案是,我必须像这样使用ROUND()
3x:ROUND(ROUND(ROUND(value, 3), 2), 1)
。第一次取整确保它不再是近似值数(其中Mysql使用"四舍五入到最近的偶数"规则),而是精确值数一个十进制3。