在 SQL UPDATE 中将值向上舍入到最接近的整数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1388835/
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
Round up value to nearest whole number in SQL UPDATE
提问by Skuta
I'm running SQL that needs rounding up the value to the nearest whole number.
我正在运行需要将值四舍五入到最接近的整数的 SQL。
What I need is 45.01 rounds up to 46. Also 45.49 rounds to 46. And 45.99 rounds up to 46, too. I want everything up one whole digit.
我需要的是 45.01 舍入到 46。还有 45.49 舍入到 46。45.99 也舍入到 46。我希望所有的东西都提高一个整数。
How do I achieve this in an UPDATE statement like the following?
如何在如下所示的 UPDATE 语句中实现这一点？
Update product SET price=Round
回答by Pascal MARTIN
You could use the ceilfunction, at least on MySQL ; this portion of SQL code :
您可以使用该ceil功能，至少在 MySQL 上；这部分 SQL 代码：
select ceil(45.01), ceil(45.49), ceil(45.99);
will get you "46" each time.
每次都会让你“46”。
For your update, so, I'd say :
对于您的更新，我想说：
Update product SET price = ceil(45.01)
BTW : On MySQL, ceilis an alias to ceiling; not sure about other DB systems, so you might have to use one or the other, depending on the DB you are using...
顺便说一句：在 MySQL 上，ceil是ceiling;的别名。不确定其他数据库系统，因此您可能必须使用一个或另一个，具体取决于您使用的数据库...
Quoting the documentation :
引用文档：
CEILING(X)Returns the smallest integer value not less than X.
CEILING(X)返回不小于 X 的最小整数值。
And the given example :
和给定的例子：
mysql> SELECT CEILING(1.23);
-> 2
mysql> SELECT CEILING(-1.23);
-> -1
回答by pjp
Try ceiling...
试试天花板...
SELECT Ceiling(45.01), Ceiling(45.49), Ceiling(45.99)
回答by Gil Allen
For MS SQL CEILING(your number) will round it up. FLOOR(your number) will round it down
对于 MS SQL CEILING（您的数字）会将其四舍五入。FLOOR（您的号码）会将其四舍五入
回答by markthewizard1234
Ceiling is the command you want to use.
天花板是您要使用的命令。
Unlike Round, Ceiling only takes one parameter (the value you wish to round up), therefore if you want to round to a decimal place, you will need to multiply the number by that many decimal places first and divide afterwards.
与 Round 不同，Ceiling 只接受一个参数（您要四舍五入的值），因此如果您想四舍五入到小数位，则需要先将数字乘以小数位数，然后再除以。
Example.
例子。
I want to round up 1.2345 to 2 decimal places.
我想将 1.2345 向上舍入到小数点后两位。
CEILING(1.2345*100)/100 AS Cost
回答by Cshah
If you want to round off then use the round function. Use ceiling function when you want to get the smallest integer just greater than your argument.
如果要四舍五入，请使用 round 函数。当您想获得刚好大于参数的最小整数时，请使用天花板函数。
For ex: select round(843.4923423423,0) from dual gives you 843 and
例如： select round(843.4923423423,0) from dual 给你 843 和
select round(843.6923423423,0) from dual gives you 844
select round(843.6923423423,0) from dual 给你 844
回答by Edwin
Combine round and ceiling to get a proper round up.
结合圆形和天花板以获得适当的圆形。
select ceiling(round(984.375000), 0)) => 984
while
尽管
select round(984.375000, 0) => 984.000000
and
和
select ceil (984.375000) => 985
回答by a1kmm
This depends on the database server, but it is often called something like CEILor CEILING. For example, in MySQL...
这取决于数据库服务器，但通常称为CEIL或 之类的东西CEILING。例如，在 MySQL...
mysql> select ceil(10.5);
+------------+
| ceil(10.5) |
+------------+
| 11 |
+------------+
You can then do UPDATE PRODUCT SET price=CEIL(some_other_field);
然后你可以做 UPDATE PRODUCT SET price=CEIL(some_other_field);
