当前位置：首页 > 站长知识 > 数据库 > 正文内容

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

2024-11-30数据库51

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
)


相关文章

PostgreSQL数据类型2024-11-06
PyMySQL数据库的使用2024-11-08
SQL中的INNER JOIN用法详解2024-11-29
SQL Server怎么导入Excel数据2024-11-29
sql中的regexp与like区别2024-11-29
SQL SERVER数据转换失败解决方法2024-11-29
SQL Server时间转换方法2024-11-29
SQL Server中如何查询最近一条记录2024-11-29
SQL中使用CONVERT函数转换数据类型2024-11-29

在线制作 ICO 图标

本站不销售产品、不代购、不提供技术支持，仅分享信息，请遵纪守法、文明上网。

Copyright © 2015-2024 国外主机测评. 闽ICP备2024075899号-2