总是在 SQL 中显示小数位？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/799151/
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
Always show decimal places in SQL?
提问by CJC
I'm working on a query which returns numeric values (currency). Some of the values are whole numbers and are being displayed as 3, 5, 2 etc whilst other numbers are coming up like 2.52, 4.50 etc.
我正在处理一个返回数值（货币）的查询。一些值是整数，显示为 3、5、2 等，而其他数字则显示为 2.52、4.50 等。
How can I force oracle to always show the decimal places?
如何强制 oracle 始终显示小数位？
Thanks
谢谢
回答by cagcowboy
回答by CJC
To enhance the answers already given, you can use:
要增强已经给出的答案，您可以使用：
TO_CHAR(your_value,'fm999.99')to prevent leading spaces____3.45becomes3.45(_indicates whitespace)TO_CHAR(your_value,'fm990.99')to force values less than1to show a leading zero.52becomes0.52TO_CHAR(your_value,'fm990.00')to force 2 decimal places, even if06.3becomes6.30(TO_CHAR(your_value,'fm990.00')||'%')to add a percentage sign18.6becomes18.60%
TO_CHAR(your_value,'fm999.99')防止前导空格____3.45变成3.45（_表示空格）TO_CHAR(your_value,'fm990.99')强制值小于1显示前导零.52变成0.52TO_CHAR(your_value,'fm990.00')强制保留 2 个小数位，即使06.3变成6.30(TO_CHAR(your_value,'fm990.00')||'%')添加百分号18.6变成18.60%
source: https://community.oracle.com/thread/968373?start=0&tstart=0
来源：https: //community.oracle.com/thread/968373?start=0&tstart=0
回答by no_one
The display and formatting of the data should be handled at the presentation layer - not the data one.
数据的显示和格式化应该在表示层处理 - 而不是数据层。
Use the facilities provided by your front end to format the values as you see fit.
使用前端提供的工具按照您认为合适的方式设置值的格式。
回答by Mustansar
The to_charfixes the decimal issue but you have to be certain about the length. If it is longer than the format provided, it will show the number as ####. If the number is shorter, then it will leave spaces before the number. e.g
在to_char你修复小数问题，但要确定有关的长度。如果它比提供的格式长，它会将数字显示为####. 如果数字较短，则数字前会留有空格。例如
to_char(123.45),'99.00')will show ####
to_char(123.45),'99.00')将会呈现 ####
and
和
to_char(123.45),'999999.00')will show ' 123.45'.
to_char(123.45),'999999.00')会显示' 123.45'。
So, if you have to export the results to CSV or Excel, these numbers will be treated as string.
因此，如果您必须将结果导出到 CSV 或 Excel，这些数字将被视为字符串。
So, I have not found any solution to it.
所以，我还没有找到任何解决方案。
回答by Mike Woodhouse
In SQL*Plus you can use the COLUMN directive to specify formatting on a per-column basis, separate from the query itself. That way you keep your query "clean" for possible other uses and still get your formatting. (In SQL*Plus at least...)
在 SQL*Plus 中，您可以使用 COLUMN 指令在每列的基础上指定格式，与查询本身分开。这样，您可以保持查询“干净”以用于可能的其他用途，并且仍然可以获得您的格式。（至少在 SQL*Plus 中...）
e.g
例如
COLUMN SAL FORMAT 99,990.99
Google for "SQL*Plus User's Guide and Reference" and you should get links to the Oracle location for your Oracle version. 10.1 is hereif that'll do. They'll probably all be about the same, mind you: I don't think SQL*Plus has changed much since I learned it in 1988 on Oracle 5.1.17...
谷歌搜索“SQL*Plus 用户指南和参考”，您应该会获得指向您的 Oracle 版本的 Oracle 位置的链接。如果可以的话，10.1 就在这里。请注意，它们可能都大致相同：我认为 SQL*Plus 自从我 1988 年在 Oracle 5.1.17 上学习 SQL*Plus 以来并没有发生太大变化......
