如何在SQL Server中获取精确小数位？实现仅保留两位小数的方法咨询

开发者阿泽

2026-5-6

嘿，我来帮你搞定SQL Server里保留两位小数的问题！你的查询结果现在是4.493000，想要得到4.49，有几个简单实用的方法可以试试：

方法1：使用ROUND函数做四舍五入

直接把整个计算结果用ROUND函数包裹，第二个参数设为2，就会自动对第三位小数进行四舍五入：

SELECT ROUND(
    (SELECT ISNULL(SUM(CONVERT(decimal(6,2),w1.Party_Weight))/CONVERT(decimal(6,2),1000),0) FROM weight1 AS w1)
    + (SELECT ISNULL(SUM(CONVERT(decimal(6,2),w2.Party_Weight))/CONVERT(decimal(6,2),1000),0) FROM weight2 AS w2)
    + (SELECT ISNULL(SUM(CONVERT(decimal(6,2),w3.Party_Weight))/CONVERT(decimal(6,2),1000),0) FROM weight3 AS w3),
    2
) AS Party_Weight

这个方法会帮你把4.493变成4.49，完全符合你的需求。

方法2：用CAST/CONVERT转换结果类型

把最终的总和转换成decimal(6,2)类型，系统会自动把多余的小数位截断或四舍五入：

-- 使用CAST的写法
SELECT CAST(
    (SELECT ISNULL(SUM(CONVERT(decimal(6,2),w1.Party_Weight))/CONVERT(decimal(6,2),1000),0) FROM weight1 AS w1)
    + (SELECT ISNULL(SUM(CONVERT(decimal(6,2),w2.Party_Weight))/CONVERT(decimal(6,2),1000),0) FROM weight2 AS w2)
    + (SELECT ISNULL(SUM(CONVERT(decimal(6,2),w3.Party_Weight))/CONVERT(decimal(6,2),1000),0) FROM weight3 AS w3)
AS decimal(6,2)) AS Party_Weight

-- 使用CONVERT的写法
SELECT CONVERT(decimal(6,2),
    (SELECT ISNULL(SUM(CONVERT(decimal(6,2),w1.Party_Weight))/CONVERT(decimal(6,2),1000),0) FROM weight1 AS w1)
    + (SELECT ISNULL(SUM(CONVERT(decimal(6,2),w2.Party_Weight))/CONVERT(decimal(6,2),1000),0) FROM weight2 AS w2)
    + (SELECT ISNULL(SUM(CONVERT(decimal(6,2),w3.Party_Weight))/CONVERT(decimal(6,2),1000),0) FROM weight3 AS w3)
) AS Party_Weight

如果你的结果可能超过decimal(6,2)的范围（比如总和超过9999.99），可以把精度调大，比如改成decimal(8,2)，避免出现溢出错误。

方法3：用FORMAT函数格式化输出（适合字符串展示）

如果你需要把结果格式化成字符串形式（比如显示为"4.49"），可以用FORMAT函数：

SELECT FORMAT(
    (SELECT ISNULL(SUM(CONVERT(decimal(6,2),w1.Party_Weight))/CONVERT(decimal(6,2),1000),0) FROM weight1 AS w1)
    + (SELECT ISNULL(SUM(CONVERT(decimal(6,2),w2.Party_Weight))/CONVERT(decimal(6,2),1000),0) FROM weight2 AS w2)
    + (SELECT ISNULL(SUM(CONVERT(decimal(6,2),w3.Party_Weight))/CONVERT(decimal(6,2),1000),0) FROM weight3 AS w3),
    'N2'
) AS Party_Weight

不过要注意，FORMAT的性能比前两种方法稍差，大数据量查询时优先用前两种。

额外建议：简化你的原查询

其实你可以把三个子查询合并成一个，逻辑更清晰也更好维护：

SELECT ROUND(
    ISNULL(SUM(CONVERT(decimal(6,2), Party_Weight))/1000, 0),
    2
) AS Party_Weight
FROM (
    SELECT Party_Weight FROM weight1
    UNION ALL
    SELECT Party_Weight FROM weight2
    UNION ALL
    SELECT Party_Weight FROM weight3
) AS combined_weights

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