如何将SQL计算的数值四舍五入保留两位小数？

核心思路是用SQL的ROUND()函数对百分比计算结果做四舍五入，指定保留2位小数后再拼接百分号。同时要注意整数除法的精度问题——两个整数直接相除会得到整数结果，所以需要先把其中一个数值转为浮点数（比如乘以1.0），保证计算出精确的小数百分比。

修改后的完整SQL代码：

SELECT
       CONCAT(ROUND((COUNT_PEOPLE_INSIDE_3MILES * 1.0 / COUNT_TOTAL_PEOPLE * 100), 2), '%') AS Percentage_People_Within_3miles
FROM
(
  SELECT COUNT(*) AS COUNT_PEOPLE_INSIDE_3MILES
  FROM CLIENT_DATA
  WHERE (ABS(C_ADD_X) BETWEEN 1 AND 3) AND (ABS(C_ADD_Y) BETWEEN 1 AND 3)
),
(
  SELECT COUNT(CLIENT_ID) AS COUNT_TOTAL_PEOPLE
  FROM CLIENT_DATA
);

关键修改说明： #

• COUNT_PEOPLE_INSIDE_3MILES * 1.0：把整数转为浮点数，避免整数除法导致的精度丢失（比如1/2直接计算会得到0，乘以1.0后得到0.5）
• ROUND(..., 2)：对百分比数值做四舍五入，第二个参数2表示保留两位小数
• 额外优化：用BETWEEN 1 AND 3替代>=1 AND <=3，让条件更简洁易读

不同数据库兼容提示 #

如果使用Oracle数据库，需要用TO_CHAR配合格式掩码实现四舍五入并拼接百分号，示例：

SELECT
       TO_CHAR((COUNT_PEOPLE_INSIDE_3MILES * 1.0 / COUNT_TOTAL_PEOPLE * 100), 'FM999.00') || '%' AS Percentage_People_Within_3miles
FROM
(
  SELECT COUNT(*) AS COUNT_PEOPLE_INSIDE_3MILES
  FROM CLIENT_DATA
  WHERE (ABS(C_ADD_X) BETWEEN 1 AND 3) AND (ABS(C_ADD_Y) BETWEEN 1 AND 3)
),
(
  SELECT COUNT(CLIENT_ID) AS COUNT_TOTAL_PEOPLE
  FROM CLIENT_DATA
);

这里FM999.00格式掩码确保显示两位小数，FM用于去除多余的前导空格。

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