萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> oracle教程 >> 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

copyright © 萬盛學電腦網 all rights reserved