如何修改SQL查询去除表中列的小数点后多余零

针对不同数据库系统，可采用以下对应方式修改查询语句：

MySQL/MariaDB #

仅去除末尾.0的场景 #

使用TRIM()函数精准移除小数点后多余的零：

SELECT 
    Name,
    TRIM(TRAILING '.0' FROM amount) AS amount,
    TRIM(TRAILING '.0' FROM Number_of_products) AS Number_of_products
FROM Table1;

保留有效小数（如123.50转为123.5）的场景 #

通过类型转换自动清理无效末尾零：

SELECT 
    Name,
    CAST(amount AS DECIMAL(10,2)) + 0 AS amount,
    CAST(Number_of_products AS DECIMAL(10,2)) + 0 AS Number_of_products
FROM Table1;

PostgreSQL #

基础去零方案 #

将数值转为文本后用TRIM()处理：

SELECT 
    Name,
    TRIM(TRAILING '.0' FROM amount::TEXT) AS amount,
    TRIM(TRAILING '.0' FROM Number_of_products::TEXT) AS Number_of_products
FROM Table1;

灵活格式化方案 #

用TO_CHAR()配合FM修饰符自动清理无效零：

SELECT 
    Name,
    TO_CHAR(amount, 'FM9999999999') AS amount,
    TO_CHAR(Number_of_products, 'FM9999999999') AS Number_of_products
FROM Table1;

SQL Server #

替换法去零 #

通过嵌套替换移除末尾零：

SELECT 
    Name,
    REPLACE(RTRIM(REPLACE(amount, '0', ' ')), ' ', '0') AS amount,
    REPLACE(RTRIM(REPLACE(Number_of_products, '0', ' ')), ' ', '0') AS Number_of_products
FROM Table1;

格式化函数方案 #

用FORMAT()的G格式说明符自动优化数值显示：

SELECT 
    Name,
    FORMAT(amount, 'G') AS amount,
    FORMAT(Number_of_products, 'G') AS Number_of_products
FROM Table1;

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