sql - Oracle SQL，仅舍入大于 'half'

Question

我需要对数字进行“半舍入”并保留 (.5)，例如:

如果数字 = (9115.7) 则 (9116)

如果数字 = (9115.5) 则 (9115.5)

如果数字 = (9115.3) 则 (9115)

Answer 1

您可以使用 case 表达式重写自己的舍入逻辑:

CREATE TABLE t
AS
SELECT 9115.7 AS col FROM dual UNION ALL
SELECT 9115.5 AS col FROM dual UNION ALL
SELECT 9115.3 AS col FROM dual;

SELECT col, CASE WHEN MOD(ABS(col),1) = 0.5 THEN col ELSE ROUND(col) END
FROM t;

<强> db<>fiddle demo

输出:

+---------+---------+
|  COL    | ROUNDED |
+---------+---------+
| 9115.7  |    9116 |
| 9115.5  |  9115.5 |
| 9115.3  |    9115 |
+---------+---------+