oracle小数点前零丢失的问题

oracle小数点前零丢失的问题

　　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