SQL Server：查询SMFeechallanhdr表浮点列小数点后超3位的记录

针对SMFeechallanhdr表中amount、adjustamount、balance amount这三个float类型列，要筛选出任意一列值小数点后超过3位的记录，可以采用以下两种可靠方案：

方案一：利用ROUND函数对比（推荐，性能更优） #

由于float是近似数值类型，直接做小数位判断容易受精度误差影响，通过ROUND函数将数值保留3位小数后，与原数值对比，设置一个极小的误差阈值来规避精度问题：

SELECT *
FROM SMFeechallanhdr
WHERE 
    -- 检查amount是否超出3位小数
    ABS(amount - ROUND(amount, 3)) > 0.0000001
    -- 检查adjustamount是否超出3位小数
    OR ABS(adjustamount - ROUND(adjustamount, 3)) > 0.0000001
    -- 检查balance amount是否超出3位小数（注意列名含空格需用方括号包裹）
    OR ABS([balance amount] - ROUND([balance amount], 3)) > 0.0000001;

原理说明 #

如果原数值的小数位不超过3位，ROUND(列名, 3)的结果与原数值的差值会极小（可忽略）；若小数位超过3位，差值会明显大于设置的阈值0.0000001，从而筛选出目标记录。

方案二：字符串转换判断（直观易理解） #

将float数值转换为固定格式的字符串，直接统计小数点后的位数：

SELECT *
FROM SMFeechallanhdr
WHERE 
    -- 处理amount列
    (
        CHARINDEX('.', STR(amount, 20, 10)) > 0 
        AND LEN(LTRIM(RTRIM(SUBSTRING(STR(amount, 20, 10), CHARINDEX('.', STR(amount, 20, 10)) + 1, 10)))) > 3
    )
    -- 处理adjustamount列
    OR (
        CHARINDEX('.', STR(adjustamount, 20, 10)) > 0 
        AND LEN(LTRIM(RTRIM(SUBSTRING(STR(adjustamount, 20, 10), CHARINDEX('.', STR(adjustamount, 20, 10)) + 1, 10)))) > 3
    )
    -- 处理balance amount列
    OR (
        CHARINDEX('.', STR([balance amount], 20, 10)) > 0 
        AND LEN(LTRIM(RTRIM(SUBSTRING(STR([balance amount], 20, 10), CHARINDEX('.', STR([balance amount], 20, 10)) + 1, 10)))) > 3
    );

原理说明 #

使用STR(列名, 20, 10)强制将float值转换为长度20、保留10位小数的字符串，避免科学计数法问题；随后通过CHARINDEX定位小数点，截取并统计小数点后的有效位数，若超过3则符合筛选条件。

内容的提问来源于stack exchange，提问作者Syed Raza Abbas SRA