如何在Oracle中进行四舍五入并保留两位小数？

Question

你好，我有一个关于数字保留两位小数的需求。如果使用 round 函数可以对数字进行四舍五入，但是如果第三个小数小于 5 ，则无法向上取整。我的需求是，不管第三个小数是多少，第二个小数都应该向上取整。这是否可能实现？

eg: 17.813 need to be 17.82

20.126 need to be 20.13

Select round(17.813,2) from dual will give 17.81

如何获取这个？

- Sachu

Answer 1

您可以将数值乘以100，使用ceil()函数将调整后的值“向上”（有点像）舍入到最接近的整数，然后再次除以100：

ceil(<your number> * 100) / 100

示例:

with t (n) as (
  select 17.813 from dual
  union all select 20.126 from dual
  union all select 1.000 from dual
  union all select 1.001 from dual
  union all select 1.005 from dual
  union all select 1.009 from dual
  union all select 1.010 from dual
)
select n, ceil(n * 100) / 100 as rounded_up
from t;

         N ROUNDED_UP
---------- ----------
    17.813      17.82
    20.126      20.13
         1          1
     1.001       1.01
     1.005       1.01
     1.009       1.01
      1.01       1.01

< p > round() 函数使用（针对正数）：

The round()函数（针对正数）：

ROUND(n, integer) = FLOOR(n * POWER(10, integer) + 0.5) * POWER(10, -integer)

因此，您可以通过类似的方式概括总结版本：

ceil(n * power(10, integer)) * power(10, -integer)

您需要考虑如何处理负值，尽管可能已经符合您的预期；将2作为“整数”值插入：

with t (n) as (
  select 17.813 from dual
  union all select 20.126 from dual
  union all select 1.000 from dual
  union all select 1.001 from dual
  union all select 1.005 from dual
  union all select 1.009 from dual
  union all select 1.010 from dual
  union all select -1.000 from dual
  union all select 0 from dual
  union all select -1.001 from dual
  union all select -1.005 from dual
  union all select -1.009 from dual
  union all select -1.010 from dual
)
select n, ceil(n * power(10, 2)) * power(10, -2) as rounded_up
from t;

         N ROUNDED_UP
---------- ----------
    17.813      17.82
    20.126      20.13
         1          1
     1.001       1.01
     1.005       1.01
     1.009       1.01
      1.01       1.01
        -1         -1
         0          0
    -1.001         -1
    -1.005         -1
    -1.009         -1
     -1.01      -1.01

- Alex Poole