MySQL 将数字格式化为 2 位小数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11190668/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Format number to 2 decimal places
提问by Tenza
I would like to know how can I output a number with 2 decimal places, without rounding the original number.
我想知道如何输出带有 2 个小数位的数字，而不舍入原始数字。
For example:
例如：
2229,999 -> 2229,99
I already tried:
我已经尝试过：
FORMAT(2229.999, 2)
CONVERT(2229.999, DECIMAL(4,2))
采纳答案by jasonlfunk
You want to use the TRUNCATEcommand.
您要使用该TRUNCATE命令。
https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_truncate
https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_truncate
回答by jmarceli
When formatting number to 2 decimal places you have two options TRUNCATEand ROUND. You are looking for TRUNCATEfunction.
将数字格式化为 2 个小数位时，您有两个选项TRUNCATE和ROUND. 您正在寻找TRUNCATE功能。
Examples:
例子：
Without rounding:
无四舍五入：
TRUNCATE(0.166, 2)
-- will be evaluated to 0.16
TRUNCATE(0.164, 2)
-- will be evaluated to 0.16
docs: http://www.w3resource.com/mysql/mathematical-functions/mysql-truncate-function.php
文档：http: //www.w3resource.com/mysql/mathematical-functions/mysql-truncate-function.php
With rounding:
四舍五入：
ROUND(0.166, 2)
-- will be evaluated to 0.17
ROUND(0.164, 2)
-- will be evaluated to 0.16
docs: http://www.w3resource.com/mysql/mathematical-functions/mysql-round-function.php
文档：http: //www.w3resource.com/mysql/mathematical-functions/mysql-round-function.php
回答by Hituptony
How about
CAST(2229.999 AS DECIMAL(6,2))to get a decimal with 2 decimal places
如何
CAST(2229.999 AS DECIMAL(6,2))获得2位小数的小数
回答by Alessandro Garcia
Just use format(number, qtyDecimals) sample: format(1000, 2) result 1000.00
只需使用 format(number, qtyDecimals) 示例：format(1000, 2) 结果 1000.00
回答by Gregory Bologna
Show as decimal Select ifnull(format(100.00, 1, 'en_US'), 0) 100.0
显示为十进制选择 ifnull(format(100.00, 1, 'en_US'), 0) 100.0
Show as Percentage Select concat(ifnull(format(100.00, 0, 'en_US'), 0), '%') 100%
显示为百分比选择 concat(ifnull(format(100.00, 0, 'en_US'), 0), '%') 100%
回答by Tye Lucas
This is how I used this is as an example:
这就是我如何使用它作为示例：
CAST(vAvgMaterialUnitCost.`avgUnitCost` AS DECIMAL(11,2)) * woMaterials.`qtyUsed` AS materialCost
