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