英文:
How to select with 2 decimal places, but keeping zero with no decimal places
问题
我试图选择一些数据并将其格式化为两位小数，但当数据为NULL或ZERO时，我希望没有小数位。
例如：
1200 应返回 1200.00
0 应返回 0
NULL 应返回 0
我尝试过：
SELECT
CASE WHEN COALESCE(number,0) = 0 THEN 0 ELSE CAST(number AS decimal(10,2)) END as "My number"
FROM Mytable
但结果都有两位小数：
1200 返回的是 1200.00
0 返回的是 0.00
NULL 返回的是 0.00
看起来它好像同时执行了THEN和ELSE，而不是其中一个...
英文:
I'm trying to select some data and format with 2 decimal places, but when the data is NULL or ZERO, I want to have no decimal places.
For example:
1200 should return 1200.00
0 should return 0
NULL should return 0
I have tried:
SELECT
CASE WHEN COALESCE(number,0) = 0 THEN 0 ELSE CAST(number AS decimal(10,2)) END as "My number"
FROM Mytable
but the results are all with 2 decimal places:
1200 is returning 1200.00
0 is returning 0.00
NULL is returning 0.00
it seems like it's executing THEN and ELSE together, not one or another...
答案1
得分: 2
你的查询适用于数字，并将其截断为指定的小数位数。当打印这些数字时，实际显示方式高度依赖于你的数据库客户端的设置。例如，正如SelVazi所演示的，在这个数据库示例中可以正常工作，但在你使用的客户端中可能不行。
如果你想完全掌控显示方式，你可以将这些数字转换为字符串，使用格式化函数 to_char：
select my_number,
case
when my_number is null or my_number = 0 then '0'
else to_char(my_number, 'FM99999990.00')
end as my_new_number
from mytable
| my_number | my_new_number |
|---|---|
| 1200 | 1200.00 |
| 0 | 0 |
| null | 0 |
英文:
Your query works on numbers, and truncates them to a given number of decimals. How these numbers are actually displayed when printed highly depends on the setup of your database client. Eg, as demonstrated by SelVazi, your query works in this db fiddle, but not in the client you use.
If you want full control over the display, you can turn these numbers to strings, using formatting function to_char:
select my_number,
case
when my_number is null or my_number = 0 then '0'
else to_char(my_number, 'FM99999990.00')
end as my_new_number
from mytable
| my_number | my_new_number |
|---|---|
| 1200 | 1200.00 |
| 0 | 0 |
| null | 0 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论