Round Up Float列到下一个数字，其倍数为5

Currently I have a column defined as a float(ex 16.98) I need to be able to take the value and find the next highest multiple of 5.

目前我有一个定义为float的列（ex 16.98）我需要能够获取该值并找到5的下一个最高倍数。

Examples...

例子...

Value 16.98           Should Return 17.00  
Value 10.46           Should Return 10.50    
Value  9.11           Should Return  9.15  
Value  8.10           Should Return  8.10  
Value  18.65          Should Return 18.65

Notice that if is is a multiple of 5 then it should return itself.

请注意，如果is是5的倍数，那么它应该返回自己。

3 个解决方案

#1

If You are using MySql try this:

如果您使用的是MySql，请尝试以下操作：

SELECT (ceil(cast(val*100 as signed)/5.0)*5.0)/100.0 from tbl;

CAST inside CEIL is very helpful - it eliminates rounding error.

CEIL内部的CAST非常有用 - 它消除了舍入误差。

Here is Demo

这是演示

In SQL SERVER:

在SQL SERVER中：

SELECT (ceiling(cast(val*100 as int)/5.0)*5.0)/100.0 from tbl;

Where tbl contains values to transform.

其中tbl包含要转换的值。

#2

SELECT Format((ceiling(cast(unitprice*100 as int)/5.0)*5.0)/100.0, 'g18')
From table

#3

Try this:

尝试这个：

SELECT
CEILING(16.98 * 20) / 20,
CEILING(10.46 * 20) / 20,
CEILING(9.11 * 20) / 20,
CEILING(8.10 * 20) / 20,
CEILING(18.65 * 20) / 20

So the query would look like:

所以查询看起来像：

SELECT CEILING(yourColumn * 20) / 20 AS Result FROM yourTable;

#1