MySQL TRUNCATE 或 ROUND 可变十进制长度 [英] MySQL TRUNCATE or ROUND with variable decimal length

问题描述

我无法在可变十进制长度上截断(或舍入)小数.这是我的 MySQL 查询:

I am not able to TRUNCATE (or round) a decimal on variable decimal lengh. Here's my MySQL query:

SELECT
TRUNCATE( `amount` , `decimal_length` ) AS truncated_decimal,
FROM table

-- sample data
amount    decimal length
123.123    0
456.456    1
789.789    2

--expected outcome
truncated_decimal
123
456.4
789.78

--current outcome (wrong)
truncated_decimal
123.123
456.456
789.789

我的 truncated_decimal 变量返回没有截断，即表中的默认十进制长度.与 ROUND 函数的行为相同.变量 decimal_length 是一个整数，对于表中的不同行不同.

My truncated_decimal variable is returned WITHOUT truncation, i.e. default decimal length from table. Same behavior with ROUND function. Variable decimal_length is an integer that is different for different rows in table.

有什么想法吗?

推荐答案

所以获得可变长度小数的正确答案是使用 FORMAT 函数:

So the correct answer to get variable length decimals is to use FORMAT function:

SELECT
FORMAT( `amount` , `decimal_length` ) AS truncated_decimal,
FROM table

它确实需要四舍五入，但至少您需要可变长度小数.

It does go with rounding, but at least you get needed variable length decimals.

这篇关于MySQL TRUNCATE 或 ROUND 可变十进制长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！