every time I want to round to 2 decimal places when it comes to zeros it doesnt want to round it... how could I round it to 2 decimal places with zeros at the end which would give me 92.00 instead of just 92 ???
每次我想要舍入到小数点后两位小数它不想舍入它...我怎么能把它四舍五入到小数点后两位小数会给我92.00而不是92？
SELECT ROUND(COLUMN_NAME,2) FROM ....
it's giving me
它给了我
COLUMN_NAME
92
but I want
但我想要
COLUMN_NAME
92.00
I used TO_CHAR and it worked
我使用了TO_CHAR并且它有效
ROUND(TO_CHAR(COLUMN_NAME),2)
thanks guys!
多谢你们！
3 个解决方案
#1
17
you may try the TO_CHAR function to convert the result
您可以尝试使用TO_CHAR函数转换结果
e.g.
例如
SELECT TO_CHAR(92, '99.99') AS RES FROM DUAL
SELECT TO_CHAR(92.258, '99.99') AS RES FROM DUAL
Hope it helps
希望能帮助到你
#2
5
Try to avoid formatting in your query. You should return your data in a raw format and let the receiving application (e.g. a reporting service or end user app) do the formatting, i.e. rounding and so on.
尽量避免在查询中进行格式化。您应该以原始格式返回数据，并让接收应用程序（例如报告服务或最终用户应用程序）进行格式化，即舍入等。
Formatting the data in the server makes it harder (or even impossible) for you to further process your data. You usually want export the table or do some aggregation as well, like sum, average etc. As the numbers arrive as strings (varchar), there is usually no easy way to further process them. Some report designers will even refuse to offer the option to aggregate these 'numbers'.
格式化服务器中的数据会使您更难（甚至不可能）进一步处理数据。您通常希望导出表或进行一些聚合，例如求和，平均等。当数字作为字符串（varchar）到达时，通常没有简单的方法来进一步处理它们。一些报告设计者甚至拒绝提供聚合这些“数字”的选项。
Also, the end user will see the country specific formatting of the server instead of his own PC.
此外，最终用户将看到服务器的国家特定格式，而不是他自己的PC。
Also, consider rounding problems. If you round the values in the server and then still do some calculations (supposing the client is able to revert the number-strings back to a number), you will end up getting wrong results.
另外，考虑舍入问题。如果您对服务器中的值进行舍入，然后仍然进行一些计算（假设客户端能够将数字字符串还原为某个数字），则最终会得到错误的结果。
#3
4
Try using the COLUMN command with the FORMAT option for that:
尝试将COLUMN命令与FORMAT选项一起使用：
COLUMN COLUMN_NAME FORMAT 99.99
SELECT COLUMN_NAME FROM ....
#1
17
you may try the TO_CHAR function to convert the result
您可以尝试使用TO_CHAR函数转换结果
e.g.
例如
SELECT TO_CHAR(92, '99.99') AS RES FROM DUAL
SELECT TO_CHAR(92.258, '99.99') AS RES FROM DUAL
Hope it helps
希望能帮助到你
#2
5
Try to avoid formatting in your query. You should return your data in a raw format and let the receiving application (e.g. a reporting service or end user app) do the formatting, i.e. rounding and so on.
尽量避免在查询中进行格式化。您应该以原始格式返回数据，并让接收应用程序（例如报告服务或最终用户应用程序）进行格式化，即舍入等。
Formatting the data in the server makes it harder (or even impossible) for you to further process your data. You usually want export the table or do some aggregation as well, like sum, average etc. As the numbers arrive as strings (varchar), there is usually no easy way to further process them. Some report designers will even refuse to offer the option to aggregate these 'numbers'.
格式化服务器中的数据会使您更难（甚至不可能）进一步处理数据。您通常希望导出表或进行一些聚合，例如求和，平均等。当数字作为字符串（varchar）到达时，通常没有简单的方法来进一步处理它们。一些报告设计者甚至拒绝提供聚合这些“数字”的选项。
Also, the end user will see the country specific formatting of the server instead of his own PC.
此外，最终用户将看到服务器的国家特定格式，而不是他自己的PC。
Also, consider rounding problems. If you round the values in the server and then still do some calculations (supposing the client is able to revert the number-strings back to a number), you will end up getting wrong results.
另外，考虑舍入问题。如果您对服务器中的值进行舍入，然后仍然进行一些计算（假设客户端能够将数字字符串还原为某个数字），则最终会得到错误的结果。
#3
4
Try using the COLUMN command with the FORMAT option for that:
尝试将COLUMN命令与FORMAT选项一起使用：
COLUMN COLUMN_NAME FORMAT 99.99
SELECT COLUMN_NAME FROM ....