MySQL 如何让decimal运算结果为整数时自动去掉小数末尾的零

核心思路 #

• 判断计算得到的总金额小数部分是否为0，为0则转换为整数返回，否则保留原有小数即可，不会损失原有精度。

不同数据库实现方案 #

MySQL #

直接在求和结果后加+0即可自动移除末尾无效的零，代码如下：

SELECT 
  SUM(CASE WHEN status = 1 THEN price * quantity WHEN status = 2 THEN price * quantity * -1 ELSE 0 END) + 0 AS total 
FROM sales;

PostgreSQL 12及以上版本 #

使用内置的trim_scale函数，专门用于移除numeric类型末尾无效的零：

SELECT 
  trim_scale(SUM(CASE WHEN status = 1 THEN price * quantity WHEN status = 2 THEN price * quantity * -1 ELSE 0 END)) AS total 
FROM sales;

SQL Server #

通过取模判断是否存在小数部分，匹配后转换对应类型：

SELECT 
  CASE WHEN total % 1 = 0 THEN CAST(total AS INT) ELSE total END AS total
FROM (
  SELECT SUM(CASE WHEN status = 1 THEN price * quantity WHEN status = 2 THEN price * quantity * -1 ELSE 0 END) AS total 
  FROM sales
) t;

补充说明 #

如果仅需前端展示不需要返回数值类型，也可以将结果转为字符串后处理末尾零，逻辑和上述判断规则一致。

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