[SQL Server][T-SQL]查詢數值欄位中有沒有小數位數的值

2016-10-27

3567
0
T-SQL

資料匯入資料庫準備驗證總數時發現有小數位，於是想要抓出有鬼的資料出列，解題答案可能有很多種，腦海中想到這個數學函數!

先準備幾筆測試資料

WITH Decimal(name, ModifiedDate, Score) 
     AS (SELECT 'David','2015-01-01',30.1         UNION ALL 
         SELECT 'Charlemagne','2016-01-01',40   UNION ALL 
         SELECT 'Caesar','2015-01-01',40.02        UNION ALL 
         SELECT 'Alexandre','2015-01-01',40)

SELECT * FROM Decimal

Result Sets

來抓鬼了!我們來用數學函數Floor:

傳回小於或等於指定數值運算式的最大整數。

WITH Decimal(name, ModifiedDate, Score) 
     AS (SELECT 'David','2015-01-01',30.1         UNION ALL 
         SELECT 'Charlemagne','2016-01-01',40   UNION ALL 
         SELECT 'Caesar','2015-01-01',40.02        UNION ALL 
         SELECT 'Alexandre','2015-01-01',40)

SELECT * FROM Decimal
 WHERE FLOOR(score) != score

結果: