T-SQL 簡易小數處理

今天因應同事提的一則需求，寫了一段 CASE WHEN 的整數與小數處理

過程中居然踩了個雷，特此記錄下來

首先，需求如下：

當內容為整數或零時則去掉尾端的小數否則就顯示原本的小數內容

若內容為 NULL 也維持不變

例如： 120.000 要顯示為 120 ， 而 120.12345 則維持不變 

在聽完需求後，我快速寫了以下這段 CASE WHEN 判斷

但看起來行不通阿！！！ 可以看到我在 THEN 的時候直接轉成INT類型，但出來的結果還是DECIMAL類型
此時同事說了句，是不是有可能型態在CASE WHEN時就已經決定了

經過測試大致整理出邏輯如下

CASE WHEN 是透過所有 THEN 值的型態優先順序，來決定最終型態為何

當其中一個型態無法做隱含轉換時(比如像下圖的文字無法直接轉換成小數) ，就會出錯

 

而透過下列的語法，可以自行測試各種組合的最終型態的優先權為何

DECLARE @T DECIMAL(10,4) = 192.1233
SELECT 
SQL_VARIANT_PROPERTY(IntAndDecimal,'BaseType'), 
IntAndDecimal
FROM 
(
SELECT 
CASE 
WHEN @T = FLOOR(@T)
THEN CAST(@T as varchar)
WHEN @T > FLOOR(@T) 
THEN CAST(@T as INT)
WHEN @T < FLOOR(@T) 
THEN CAST(@T as decimal)
END as IntAndDecimal 
) X　　

 從下圖可以看到當CASE WHEN 同時有 varchar 、 int 、 decimal 、 float 時

最後會統一轉換成float型態 

 

相關的文件可以參考官網
https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017

最後

以下就是我寫的完整語法，可以看到透過在 THEN 我分邊加了兩個 CAST 轉換

在優先權的比較後，最後的型態會統一轉換成  Float ，所以原本會以 DECIMAL 為主的型態已經消失了

相關的效果也可以參考兩個 Column  的值 ，最右邊為預期的結果，左邊則為原始結果