oracle查询不显示小数点前的0
2024-08-31 08:43:09
1.问题起源
oracle 数据库字段值为小于1的小数时,使用char类型处理,会丢失小数点前面的0
例如0.35就变成了.35
2.解决办法:用to_char函数格式化数字显示
select to_char(0.338,'fm9999999990.00') from dual;
结果:0.34
这里重点要看 fm9999999999.99,表示整数部分最多10位,小数部分2位,fm表示去 掉转位字符串后前面的空格,不加fm,0.34前面会有空格的.
3.with的使用
WITH TMP1 AS (
SELECT 1 AS A, 2 AS B FROM DUAL
UNION
SELECT 1 AS A, 3 AS B FROM DUAL
UNION
SELECT 1 AS A, 4 AS B FROM DUAL
),
TMP2 AS (
SELECT 1 AS A, 2 AS B FROM DUAL
UNION
SELECT 1 AS A, 3 AS B FROM DUAL
UNION
SELECT 2 AS A, 4 AS B FROM DUAL
)
SELECT TMP1.*,TMP2.*
FROM TMP1 JOIN TMP2
ON TMP1.A = TMP2.A
作者“努力向前”
经过测试,怀疑是number转为char时,舍去了小数点前面的0
- SQL> select to_char(num,'999999999.999999999') from ml_test;
- TO_CHAR(NUM,'999999999.9999999
- ------------------------------
- .421240000
- .421246543
- 65432.421243240
- 4.621240000
- SQL> select to_char(num) from ml_test;
- TO_CHAR(NUM)
- ----------------------------------------
- .42124
- .4212465434
- 65432.42124324
- 4.62124
- SQL> select to_char(0.99) from dual;
- TO_CHAR(0.99)
- -------------
- .99
--4.怎么解决,解决方案如下
(1)
- SQL> select to_nmber(to_char(0.99)) from dual;
- TO_NUMBER(TO_CHAR(0.99))
- ------------------------
- 0.99
- SQL> select to_char(num,'fm999999990.999999999') from ml_test;
- TO_CHAR(NUM,'FM999999990.99999
- ------------------------------
- 0.42124
- 0.421246543
- 65432.42124324
- 4.62124
(2)使用case when 或 decode函数,取第一位是否为".", 补0 ,就ok了
- SQL> select to_char(num,'999999999.999999999') from ml_test;
- TO_CHAR(NUM,'999999999.9999999
- ------------------------------
- .421240000
- .421246543
- 65432.421243240
- 4.621240000
- SQL> select to_char(num) from ml_test;
- TO_CHAR(NUM)
- ----------------------------------------
- .42124
- .4212465434
- 65432.42124324
- 4.62124
- SQL> select to_char(0.99) from dual;
- TO_CHAR(0.99)
- -------------
- .99
最新文章
- 【干货分享】流程DEMO-采购预算编制
- JAVA面试题1
- Matlab的标记分水岭分割算法
- 如何整合最新的Flex sdk和Air sdk。
- ligerui_ligerTree_004_对";ligerTree";节点操作
- linux mount (挂载命令)详解
- makefile中的target到底代表什么?
- python del函数
- 性能测试分享:Jmeter的api监控工具解决方案
- 30分钟掌握 C#7
- AngularJS 动画总结
- hdu5444(模拟)
- mysql服务无法正常启动
- MyBatis探究-----为实体类Bean取别名,配置typeAliases
- 关于Struts2中 Action 配置method的解读
- session(概念、session对象的获取、删除、验证)
- 通过shell查找访问日志中访问量最大的ip
- Go Example--结构体
- Scala深入浅出实战经典:29,Case class和Case object代码实战解析
- elasticsearch5.5安装