SQL Server中修正含税运费计算后小数位多0的问题

问题重现 #

在计算含税运费时，使用以下代码（sol1）会得到小数位后带多余0的结果，例如140.19会显示为140.1900：

CREATE TABLE Orders
(
    OrderID INT PRIMARY KEY,
    Freight MONEY,
);
DROP TABLE Orders
INSERT INTO Orders
VALUES
(10270, 136.54),
(10271, 4.54),
(10272, 98.03),
(10273, 76.07);

-- 出现多余小数0的代码（sol1）
SELECT OrderID,
       Freight,
       CASE
           WHEN Freight >= 100 THEN '10%' ELSE '5%'
           END AS tax,
       CASE
           WHEN Freight >= 100 THEN Freight * 1.1 ELSE Freight * 1.05
       END AS [Freight with tax]
FROM Orders

问题根源：Freight字段为MONEY类型，SQL Server中该类型默认保留4位小数，计算后结果会自动补0填充到4位小数位。

修复方案 #

方案1：修改sol1，转换结果数据类型 #

将计算后的结果转换为DECIMAL类型，指定精度和小数位数（例如DECIMAL(10,2)保留两位小数）：

SELECT OrderID,
       Freight,
       CASE
           WHEN Freight >= 100 THEN '10%' ELSE '5%'
           END AS tax,
       CAST(CASE
           WHEN Freight >= 100 THEN Freight * 1.1 ELSE Freight * 1.05
       END AS DECIMAL(10,2)) AS [Freight with tax]
FROM Orders

也可以用CONVERT函数实现同样效果：

SELECT OrderID,
       Freight,
       CASE
           WHEN Freight >= 100 THEN '10%' ELSE '5%'
           END AS tax,
       CONVERT(DECIMAL(10,2), CASE
           WHEN Freight >= 100 THEN Freight * 1.1 ELSE Freight * 1.05
       END) AS [Freight with tax]
FROM Orders

方案2：使用FORMAT函数格式化输出 #

如果仅需显示时去掉多余0，可使用FORMAT函数指定输出格式：

SELECT OrderID,
       Freight,
       CASE
           WHEN Freight >= 100 THEN '10%' ELSE '5%'
           END AS tax,
       FORMAT(CASE
           WHEN Freight >= 100 THEN Freight * 1.1 ELSE Freight * 1.05
       END, 'N2') AS [Freight with tax]
FROM Orders

注意：FORMAT返回字符串类型，若需后续数值计算，优先选择方案1的类型转换。

方案3：简化计算逻辑并转换类型 #

直接用数值计算税率，同时转换结果类型，避免字符串依赖：

SELECT OrderID,
       Freight,
       CASE WHEN Freight >= 100 THEN '10%' ELSE '5%' END AS tax,
       CAST(Freight * (CASE WHEN Freight >= 100 THEN 1.1 ELSE 1.05 END) AS DECIMAL(10,2)) AS [Freight with tax]
FROM Orders

预期结果 #

以上方案均可得到两位小数的正确结果：

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