MySQL SQL 中的小数位
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5332583/
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
Decimal places in SQL
提问by kburns
I am calculating percentages. One example is coming down to 38589/38400
我正在计算百分比。一个例子是下降到 38589/38400
So the percentage is 100*(38589/38400) which equals something like 100.4921875, but the result shows up as 100.
所以百分比是 100*(38589/38400)，等于 100.4921875，但结果显示为 100。
How can I get it to be displayed with x number of decimals?
如何让它以 x 位小数显示？
Similarly, will the same work if i'd like 2 to be displayed as 2.000000?
同样，如果我希望 2 显示为 2.000000，同样的工作吗？
Thanks!
谢谢！
回答by RichardTheKiwi
You can cast it to a specific data type, which preserves the data type as well as rounding to a certain precision
您可以将其强制转换为特定的数据类型，从而保留数据类型并四舍五入到一定的精度
select cast(100*(38589/38400) as decimal(10,4))
FYI
供参考
select 100*(38589/38400)
# returns 100.4922, not 100 for me
select cast(2 as decimal(20,6))
# output : 2.000000
回答by Abe Miessler
With regards to your number formatting have you looked at the format function:
关于您的数字格式，您是否查看了格式功能：
mysql> SELECT FORMAT(12332.123456, 4);
-> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
-> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
-> '12,332'
so to get 2.000000 from 2 do:
所以要从 2 中获得 2.000000，请执行以下操作：
SELECT FORMAT(2,6);
Also, according to mySQL's documentation regarding division:
另外，根据mySQL关于除法的文档：
In division performed with /, the scale of the result when using two exact-value operands is the scale of the first operand plus the value of the div_precision_increment system variable (which is 4 by default). For example, the result of the expression 5.05 / 0.014 has a scale of six decimal places (360.714286).
These rules are applied for each operation, such that nested calculations imply the precision of each component. Hence, (14620 / 9432456) / (24250 / 9432456), resolves first to (0.0014) / (0.0026), with the final result having 8 decimal places (0.60288653).
在使用 / 执行的除法中，使用两个精确值操作数时结果的小数位数是第一个操作数的小数位数加上 div_precision_increment 系统变量的值（默认为 4）。例如，表达式 5.05 / 0.014 的结果有六个小数位 (360.714286)。
这些规则适用于每个操作，因此嵌套计算意味着每个组件的精度。因此，(14620 / 9432456) / (24250 / 9432456) 首先解析为 (0.0014) / (0.0026)，最终结果有 8 个小数位 (0.60288653)。
This would lead me to agree with @Cyberwiki regarding the result you would see from your division.
这将使我同意@Cyberwiki 关于您从您的部门看到的结果。
回答by Ken White
You need to convert one of the types to floating point:
您需要将其中一种类型转换为浮点数：
SELECT 100.0 * ((338589 * 1.0)/38400) ...
回答by Mahmood AL Fadel
With regards to the reason why the result shows 100 instead of 100.4921875 is maybe related to the type of the corresponding column assuming that you store the result in a table column. Make sure that the type of that column is Double.
关于结果显示 100 而不是 100.4921875 的原因可能与假设您将结果存储在表列中的相应列的类型有关。确保该列的类型为 Double。
If you'd like 2 to be displayed as 2.000000, just multiply it by 1.0 as follows:
如果您希望 2 显示为 2.000000，只需将其乘以 1.0，如下所示：
(select (100*(38589/38400*1.0))
and the output will show: 100.49219
输出将显示：100.49219
