oracle 在oracle中将数字四舍五入为逗号后的2位数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36764286/
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
Rounding numbers to 2 digits after comma in oracle
提问by MaMu
I'm looking for any way to be able to round or trunc the numbers to 2 digits after comma. I tried with round, truncand to_char. But didn't get what I wanted.
我正在寻找能够在逗号后将数字四舍五入或截断为 2 位的任何方法。我试过round,trunc和to_char。但是没有得到我想要的。
select round(123.5000,2) from dual;
select round(123.5000,2) from dual;
Works fine, but when I have zero as second digit after comma, I get only one 1 digit after comma in output number
工作正常，但是当我在逗号后的第二位数字为零时，输出编号中的逗号后只有一位数字
select to_char(23.5000, '99.99') from dual;
Works fine, but if the number before comma has 3 digits, I'm getting '###' as output. Apart from that I'm getting here spaces at the beginning. Is there any clear way to remove these spaces?
工作正常，但如果逗号前的数字有 3 位数字，我将得到 '###' 作为输出。除此之外，我一开始就有空间。有什么明确的方法可以删除这些空格吗？
I'm looking a way to always get a number with two digits after comma and for all numbers(1,10,100 etc).
我正在寻找一种方法来始终获得逗号后两位数字和所有数字（1,10,100 等）的数字。
回答by Alex Poole
You can use the FMnumber format modifierto suppress the leading spaces, but note that you also then need to use .00rather than .99, and you may want the last element of the format model before the decimal point to be a zero too if you want numbers less that 1 to be shown as, say, 0.50instead of .50:
您可以使用的FM数字格式修饰符来抑制前导空格，但要注意，你还那么就需要使用.00，而不是.99，你可能需要小数点前的业态模式的最后一个元素是零太多，如果你想数少那个 1 显示为，比如说，0.50而不是.50：
with t (n) as (
select 123.5678 from dual
union all select 123.5000 from dual
union all select 23.5000 from dual
union all select 0 from dual
union all select 1 from dual
union all select 10 from dual
union all select 100 from dual
)
select n,
round(n, 2) as n2,
to_char(round(n, 2), '99999.99'),
to_char(round(n, 2), 'FM99999.00') as str2,
to_char(round(n, 2), 'FM99990.00') as str3
from t;
N N2 TO_CHAR(R STR2 STR3
---------- ---------- --------- --------- ---------
123.5678 123.57 123.57 123.57 123.57
123.5 123.5 123.50 123.50 123.50
23.5 23.5 23.50 23.50 23.50
0 0 .00 .00 0.00
1 1 1.00 1.00 1.00
10 10 10.00 10.00 10.00
100 100 100.00 100.00 100.00
You don't strictly need the round()as well since that's the default behaviour, but it doesn't hurt to be explicit (aside from a tiny performance impact form the extra function call, perhaps).
您也不需要严格round()，因为这是默认行为，但明确表示并没有什么坏处（除了额外的函数调用对性能的微小影响，也许）。
This gives you a string, not a number. A number does not have trailing zeros. It doesn't make sense to describe an actual number in those terms. It only makes sense to have the trailing zeros when you're converting the number to a string for display.
这给你一个字符串，而不是一个数字。数字没有尾随零。用这些术语描述实际数字是没有意义的。当您将数字转换为字符串以进行显示时，只有尾随零才有意义。
回答by Aleksej
You can use:
您可以使用：
select to_char(23.5000, 'fm999999999.00')
from dual
The 'fm'avoids the leading spaces, and the '00'says to always write two digits after the comma, even if they are 0.
This will return a string with your format.
在'fm'避免了前导空格，并'00'说逗号后总是写两位数字，即使是0。这将返回一个字符串的格式。
If you need to round the number, not only truncating the digits after the second one, you can apply the to_charto the round()of your number
如果你需要数字的舍入，不仅截断数字在第二个之后，你可以应用to_char到round()你的电话号码的
回答by rakwaht
Have you tried with a cast operation?
您是否尝试过使用强制转换操作？
You can try for example with this, obviously substituting 30,2 with your desired precision.
例如，您可以尝试使用此方法，显然用您想要的精度替换 30,2。
SELECT CAST (someNumber AS DECIMAL(30,2)) FROM dual
You can find the documentation of the cast operation for the oracle sql here
您可以在此处找到 oracle sql 的强制转换操作的文档
回答by Factory Girl
Try COLUMNcommand with FORMAToption.
尝试COLUMN带有FORMAT选项的命令。
COLUMN COLUMN_NAME FORMAT 99.99
SELECT COLUMN_NAME FROM TABLE_NAME ..
