SQL Server浮点数计算精度问题：FinalProfit还原值不符

在SQL Server中，通过传入的@FinalProfit反向计算ProfitRate并存储，再用ProfitRate还原@FinalProfit时出现精度偏差——例如当@FinalProfit为50时，还原后得到49.94，不符合价格计算的精度要求。

核心计算逻辑：

• 正向计算：@FinalProfit = (ProfitRate / 100) * SoldPrice
• 反向计算（当前存在问题的逻辑）：CAST(((@FinalProfit / SoldPrice) * 100) AS DECIMAL(15,2))

复现场景 #

saleinfo表初始数据：

ID SoldPrice ProfitRate
1 300.00    0.0000
2 300.00    0.0000
3 325.00    0.0000
4 400.00    0.0000
5 500.00    0.0000

查询还原@FinalProfit的代码：

SELECT (ProfitRate / 100) * SoldPrice AS FinalProfit
FROM saleinfo
WHERE ID = 1

当前更新ProfitRate的存储过程：

ALTER PROCEDURE [dbo].[inlineedit]
    (
    @InvoiceItemID      INT,
    @FinalProfit      DECIMAL(15,2)
    )
AS
BEGIN TRY
    BEGIN
        IF @FinalProfit IS NOT NULL
            UPDATE [dbo].[LotSale]
            SET ProfitRate =  
                CASE WHEN SoldPrice <> 0 AND SoldPrice IS NOT NULL THEN CAST(((@FinalProfit / SoldPrice) * 100) AS DECIMAL(15,2))
                ELSE 0
                END 
            WHERE LotID IN (
                SELECT L.LotID
                FROM dbo.Lot AS L
                    INNER JOIN dbo.LotSale AS LS ON LS.LotID = L.LotID
                    INNER JOIN dbo.InvoiceItem AS II ON II.LotSaleID = LS.LotSaleID
                WHERE II.InvoiceItemID = @InvoiceItemID
            )  
    END
    COMMIT ;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION ;
    DECLARE @TErrMsg NVARCHAR(1000) ;
    SET @TErrMsg = (SELECT ERROR_MESSAGE() AS ErrMsg) ;
    RAISERROR(@TErrMsg, 18, 1) ;
END CATCH ;
GO

问题原因 #

1. 字段精度不足：ProfitRate字段使用DECIMAL(15,2)，仅保留2位小数，反向计算时的无限循环小数（如50/300*100≈16.666...）会被截断，导致后续正向计算出现偏差。
2. 计算顺序放大误差：先执行除法@FinalProfit / SoldPrice会先产生小数截断，再乘以100进一步放大了精度损失。

解决方案 #

方案1：提高ProfitRate的存储精度 #

将ProfitRate字段的数据类型改为DECIMAL(15,4)（或更高精度，根据业务需求），保留更多小数位以减少截断误差。

修改表结构示例：

ALTER TABLE LotSale ALTER COLUMN ProfitRate DECIMAL(15,4) NOT NULL;

方案2：调整计算顺序并优化截断方式 #

将反向计算逻辑改为先乘法后除法，避免中间步骤的精度损失；同时使用ROUND替代直接CAST，确保四舍五入而非强制截断：

SET ProfitRate =  
    CASE WHEN SoldPrice <> 0 AND SoldPrice IS NOT NULL THEN ROUND((@FinalProfit * 100) / SoldPrice, 4)
    ELSE 0
    END 

方案3：直接存储@FinalProfit（业务允许时推荐） #

如果业务无需强制存储ProfitRate百分比，直接在表中新增FinalProfit字段存储传入的值，完全避免反向计算的精度问题。

修改后的存储过程示例 #

ALTER PROCEDURE [dbo].[inlineedit]
    (
    @InvoiceItemID      INT,
    @FinalProfit      DECIMAL(15,2)
    )
AS
BEGIN TRY
    BEGIN TRANSACTION;
        IF @FinalProfit IS NOT NULL
            UPDATE [dbo].[LotSale]
            SET ProfitRate =  
                CASE WHEN SoldPrice <> 0 AND SoldPrice IS NOT NULL THEN ROUND((@FinalProfit * 100) / SoldPrice, 4)
                ELSE 0
                END 
            WHERE LotID IN (
                SELECT L.LotID
                FROM dbo.Lot AS L
                    INNER JOIN dbo.LotSale AS LS ON LS.LotID = L.LotID
                    INNER JOIN dbo.InvoiceItem AS II ON II.LotSaleID = LS.LotSaleID
                WHERE II.InvoiceItemID = @InvoiceItemID
            )  
    COMMIT TRANSACTION ;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION ;
    DECLARE @TErrMsg NVARCHAR(1000) ;
    SET @TErrMsg = ERROR_MESSAGE() ;
    RAISERROR(@TErrMsg, 18, 1) ;
END CATCH ;
GO

内容的提问来源于stack exchange，提问作者Mateen Bagheri