星期五, 7月 22, 2011

[SQL] 數學運算-除法

當一個運算子(EX:加減乘除四則運算)結合兩個不同資料類型的運算式(EX:被乘法乘法、被除數除數)時,資料類型優先順序的規則,會指定將低優先順序的資料類型,轉換為高優先順序的資料類型。如果轉換不是支援的隱含轉換,就會傳回錯誤。如果這兩個運算元運算式的資料類型相同,則作業結果就含有該資料類型。

除法 Dividend / Divisor

製作報表時,常常需要用百分比表示,因此會利用到除法來進行計算,使用除法有兩件注意事項
    分母不得為零
當 T-SQL 語法中分母為零會出現 發現除以零錯誤 的錯誤訊息。
SELECT 1.0 / 0 -- 模擬除以零錯誤
    NULLIF() 說明:
  • 語法:NULLIF ( express1 , express2 )
  • 傳回類型:當 express1 等於 express2 時,會傳回 NULL;當 express1 不等於 express2 時,傳回 express1 值。
利用 NULLIF() 函數來處理分母,把分母為零轉換成 NULL ,搭配 NULL 本身的特性(任何數值除以 NULL 結果為 NULL ),就可以避免錯誤發生,而 NULLIF() 其實也算是 CASE WHEN 的應用之一。
SELECT 
      1.0 / NULLIF(T.[分母], 0) AS [利用 NULLIF], 
      1.0 / CASE WHEN T.[分母] = 0 THEN NULL ELSE T.[分母] END AS [利用 CASE WHEN 1],
      CASE WHEN T.[分母] = 0 THEN NULL ELSE 1.0 / T.[分母] END AS [利用 CASE WHEN 2],
      CASE WHEN T.[分母] = 0 THEN 0 ELSE 1.0 / T.[分母] END AS [利用 CASE WHEN 3],
      CASE WHEN T.[分母] = 0 THEN '分母為0' ELSE '分母不為0' END AS [文字敘述]
FROM 
      (
          SELECT 0 AS [分母]
          UNION ALL
          SELECT 3
      ) T
除零錯誤

    整數相除
整數 Dividend 除以整數 Divisor,結果就是整數,結果的任何小數點後數字部份都會截斷,因此要獲得百分比,必須先把 int 轉換成 numeric,根據資料型態優先順序,就會有小數點產生。
SELECT 
  T.[算式],
  T.[結果]
FROM
  (
    SELECT '10 / 3' AS [算式] , 10 / 3 AS [結果]
    UNION ALL
    SELECT '10.0 / 3', 10.0 / 3
    UNION ALL 
    SELECT '10 / 3.0' , 10 / 3.0
    UNION ALL 
    SELECT 'CAST(10 AS numeric) / 3 ' , CAST(10 AS numeric) / 3
    UNION ALL 
    SELECT '(CAST(1 AS numeric) * 10) / 3' , (CAST(1 AS numeric) * 10) / 3
    UNION ALL 
    SELECT '(1.0 * 10) / 3' , (1.0 * 10) / 3
    UNION ALL 
    SELECT '10 / (3 * 1.0 )' , 10 / (3 * 1.0 )
  ) AS T
數學運算 2

沒有留言:

張貼留言