mysql - SQL 货币四舍五入到最接近的 0.05 美分

Question

我正在尝试将 MySQL SELECT 中的钱四舍五入到最接近的 0.05 美分。

所以数字像:

140.70  should become 140.70
140.71  should become 140.70
140.72  should become 140.70
140.73  should become 140.75
140.74  should become 140.75
140.75  should become 140.75
140.76  should become 140.75
140.77  should become 140.75
140.78  should become 140.80
140.79  should become 140.80

所以更详细

0.00 = 0.00
0.01 = 0.00
0.02 = 0.00
0.022 = 0.00   // here the magic should happen 0.022 is closer to 0, so result is 0
0.023 = 0.05   // but 0.023 should be rounded to 0.05! cause first round 0.023 to 0.025 which should then be rounded up to 0.05
0.03 = 0.05

我用 MySQL CEIL() 尝试了一些不同的东西和 MySQL FLOOR()但得不到正确的结果。

创建了一个 SQL Fiddle here

有一个毫无意义的表，除了我们需要一个从中选择:

CREATE TABLE hello ( world varchar(255) );
INSERT INTO hello (world) VALUES ('blubb');

这是选择查询:

SELECT 

 CEILING ( 0.05 / 0.05 ) * 0.05 AS CEIL_1,
 CEILING ( 0.06 / 0.05 ) * 0.05 AS CEIL_2,
 CEILING ( 0.07 / 0.05 ) * 0.05 AS CEIL_3,
 CEILING ( 0.08 / 0.05 ) * 0.05 AS CEIL_4,
 CEILING ( 0.09 / 0.05 ) * 0.05 AS CEIL_5

FROM hello;

这里有人告诉我如何正确地做吗？

Answer 1

SELECT ROUND(140.77/5,2) * 5;
+-----------------------+
| ROUND(140.77/5,2) * 5 |
+-----------------------+
|                140.75 |
+-----------------------+