Oracle数据库中保留小数点后两位的问题

Oracle数据库中保留小数点后两位的问题

1.数字保留两位小数；（小数点左侧数字可能比较大，比如10000000.12）

to_char() 函数转换

答案

select trim(to_char(85.7323232, '9999999990.00')) as 两位小数 from dual;

写法对比

select result,
       to_char(result, '9999.99') as "位数不足",
       to_char(result, '9999999999.99') as "位数充足",
       to_char(result, '0000.00') as "0的位数不足",
       to_char(result, '0000000000.00') as "0的位数充足",
       to_char(result, '9999999990.00') as "9的位数充足",
       trim(to_char(result, '9999999990.00')) as "9的位数充足且去掉左侧"
from (
    select 0 result from dual union
    select 1 result from dual union
    select 123 result from dual union
    select 123.4 result from dual union
    select 12345.67 result from dual union
    select 12345678.2 result from dual
)

2.百分比保留两位小数；（小数点左侧数字数字小于等于100，比如86.63%）

答案

select trim(to_char(0.7323232 * 100, '99990.99'))||'%' as 百分比 from dual;

select trim(to_char(0.7323232 * 100, '99990.99'))||'%' as 百分比 from dual;

写法对比

select result,
       to_char(result * 100, '99.99') || '%' as "9位数不足",
       to_char(result * 100, '00.99') || '%' as "0位数不足",
       to_char(result * 100, '99990.99') || '%' as "百分比",
       trim(to_char(result * 100, '99990.99')) || '%' as "去左侧空格的百分比",
       to_char(result * 100, 'fm99990.99') || '%' as "使用fm前缀的格式百分比",
       to_char(result * 100, 'fm99990.00') || '%' as "使用fm前缀的格式百分比2"
from (
    select 0 result from dual union
    select 1 result from dual union
    select 0.12345 result from dual union
    select 0.2 result from dual union
    select 0.23 result from dual union
    select 0.234 result from dual union
    select 0.2345 result from dual union
    select 0.23454 result from dual union
    select 0.23456 result from dual
)

鸣谢

结果集中保留两位小数的问题

---

写在最后

谁说我抄作业的，你过来我请你吃拳头拌饭

天下英雄出我辈，一入江湖岁月催
我是「无间行者」，努力把实践过的解决方案分享给大家
如果这篇文章对你有用，一个赞、一个评论、一个关注，我都很开心
码字不易，给点鼓励吧，让我知道你在看