Oracle数据库中保留小数点后两位的问题
1.数字保留两位小数
（小数点左侧数字可能比较大，比如10000000.12）
to_char() 函数转换
答案
1 | select trim(to_char(85.7323232, '9999999990.00')) as 两位小数 from dual; |
写法对比
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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%）
答案
1 | select trim(to_char(0.7323232 * 100, '99990.99'))||'%' as 百分比 from dual; |
1 | select trim(to_char(0.7323232 * 100, '99990.99'))||'%' as 百分比 from dual; |
写法对比
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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) |
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持IT俱乐部。
