如何在mysql中仅当数字不四舍五入时才获取小数

发布于 2024-11-05 05:49:15 字数 173 浏览 2 评论 0原文

我试图获取一个格式为具有 2 位小数（如果该数字不是整数）的数字，如果该数字是整数则没有任何小数。目前我正在使用以下查询

SELECT round(SUM(number),2) as Numb FROM table

，我得到像 1.00 这样的数字，我希望它是 1

原文

I'm trying to get a number formated as having 2 decimals if the number is not an integer and not having any if the number is an integer. Currently I'm using the following query

SELECT round(SUM(number),2) as Numb FROM table

And I get numbers like 1.00 which I'd like it to be 1

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

岁月静好 2024-11-12 05:49:15

您必须使用带模数的案例。类似于：

select 
case (number mod 1 > 0)
  when true then round(number, 2)
  else round(number, 0)
end
from Numb

结果：

select  
case (number mod 1 > 0)  
  when true then round(number, 2)   
  else round(number,0) 
end as num 
from Numb;

+------+
| num  |
+------+
|    2 |
| 1.01 |
|    5 |
| 3.01 |
| 4.25 |
+------+
5 rows in set (0.00 sec)

You'll have to use a case with modulus. Something like:

select 
case (number mod 1 > 0)
  when true then round(number, 2)
  else round(number, 0)
end
from Numb

Results:

select  
case (number mod 1 > 0)  
  when true then round(number, 2)   
  else round(number,0) 
end as num 
from Numb;

+------+
| num  |
+------+
|    2 |
| 1.01 |
|    5 |
| 3.01 |
| 4.25 |
+------+
5 rows in set (0.00 sec)

回复收藏 0 原文

~没有更多了~