SQL 轮函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1108742/
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
SQL Round Function
提问by hrishi
round(45.923,-1)gives a result of 50. Why is this? How it is calculated?
round(45.923,-1)给出的结果是 50。这是为什么呢？它是如何计算的？
(sorry guys i was mistaken with earlier version of this question suggesting value was 46)
（对不起，我误认为这个问题的早期版本建议值为 46）
回答by S M Kamran
The SQL ROUND() function rounds a number to a precision...
SQL ROUND() 函数将数字四舍五入到精度...
For example:
例如：
round(45.65, 1) gives result = 45.7
round(45.65, 1) 给出结果 = 45.7
round(45.65, -1) gives result = 50
round(45.65, -1) 给出结果 = 50
because the precision in this case is calculated from the decimal point. If positive then it'll consider the right side number and round it upwards if it's >= 5, and if <=4 then round is downwards... and similarly if it's negative then the precision is calculated for the left hand side of decimal point... if it's >= 5
因为这种情况下的精度是从小数点开始计算的。如果是正数，那么它会考虑右边的数字，如果 >= 5，则将其向上舍入，如果 <=4，则向下舍入......同样，如果它是负数，则计算小数点左侧的精度点...如果它 >= 5
for example round(44.65, -1) gives 40 but round(45.65, -1) gives 50...
例如 round(44.65, -1) 给出 40 但 round(45.65, -1) 给出 50 ...
回答by Elzo Valugi
ROUND(748.58, -1) 750.00
回合 (748.58, -1) 750.00
the second parameter: Lenght, is the precision to which numeric_expression is to be rounded. length must be an expression of type tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.
第二个参数：长度，是 numeric_expression 的四舍五入精度。length 必须是 tinyint、smallint 或 int 类型的表达式。当 length 为正数时，numeric_expression 将四舍五入为 length 指定的小数位数。当长度为负数时，numeric_expression 在小数点左侧四舍五入，如长度所指定。
回答by markus
It is expected to be 50.
预计为50。
round(45.923, 0) => 46
回合（45.923，0）=> 46
expl: the last non-decimal digit is rounded (5), the desicion is based on the next digit (9) 9 is in the high half, ergo 5 is rounded up to 6
expl：最后一个非十进制数字四舍五入（5），desicion基于下一个数字（9）9在高半，ergo 5四舍五入到6
round(45.923, 1) => 45.9
回合（45.923，1）=> 45.9
expl: the first decimal digit is rounded (9), the desicion is based on the next digit (2) 2 is in the low half, ergo 9 stays 9
expl：第一个十进制数字四舍五入（9），desicion基于下一个数字（2）2在下半部分，ergo 9保持9
your case:round(45.923, 1-) => 45.92
你的情况：round(45.923, 1-) => 45.92
expl: the secon-last non-decimal digit is rounded (4), the desicion is based on the next digit (5) 5 is in the top half, ergo 4 is rounded up to 5, the rest of the digist are filled with 0s
expl：倒数第二个非十进制数字四舍五入（4），desicion基于下一个数字（5）5在上半部分，ergo 4四舍五入为5，其余数字填充0秒
回答by outis
As for how, start by considering how you'd round a (positive) float to the nearest integer. Casting a float to an int truncates it. Adding 0.5 to a (positive) float will increment the integer portion precisely when we want to round up (when the decimal portion >= 0.5). This gives the following:
至于如何，首先考虑如何将（正）浮点数四舍五入到最接近的整数。将浮点数转换为 int 会截断它。当我们想要四舍五入时（当小数部分 >= 0.5 时），将 0.5 添加到（正）浮点数将精确地增加整数部分。这给出了以下内容：
double round(double x) {
return (long long)(x + 0.5);
}
To add support for the precision parameter, note that (for e.g. round(123456.789, -3)) adding 500 and truncating in the thousands place is essentially the same as adding 0.5 and to rounding to the nearest integer, it's just that the decimal point is in a different position. To move the radix point around, we need left and right shift operations, which are equivalent to multiplying by the base raised to the shift amount. That is, 0x1234 >> 3is the same as 0x1234 / 2**3and 0x1234 * 2**-3in base 2. In base 10:
要添加对 precision 参数的支持，请注意（例如round(123456.789, -3)）添加 500 并在千位上截断与添加 0.5 并四舍五入到最接近的整数基本相同，只是小数点位于不同的位置。要四处移动小数点，我们需要向左和向右移动操作，这相当于乘以提高到移动量的基数。也就是说，与基数 2 中的和0x1234 >> 3相同。 在基数 10 中：0x1234 / 2**30x1234 * 2**-3
123456.789 >> 3 == 123456.789 / 10**3 == 123456.789 * 10**-3 == 123.456789
For round(123456.789, -3), this means we can do the above multiplication to move the decimal point, add 0.5, truncate, then perform the opposite multiplication to move the decimal point back.
对于round(123456.789, -3)，这意味着我们可以进行上述乘法移动小数点，加0.5，截断，然后执行相反的乘法将小数点移回。
double round(double x, double p) {
return ((long long)((x * pow10(p))) + 0.5) * pow10(-p);
}
Rounding by adding 0.5 and truncating works fine for non-negative numbers, but it rounds the wrong way for negative numbers. There are a few solutions. If you have an efficient sign()function (which returns -1, 0 or 1, depending on whether a number is <0, ==0 or >0, respectively), you can:
通过添加 0.5 和截断对非负数进行四舍五入适用于非负数，但对于负数则以错误的方式四舍五入。有几种解决方案。如果您有一个高效sign()函数（返回 -1、0 或 1，分别取决于数字是 <0、==0 还是 >0），您可以：
double round(double x, double p) {
return ((long long)((x * pow10(p))) + sign(x) * 0.5) * pow10(-p);
}
If not, there's:
如果没有，还有：
double round(double x, double p) {
if (x<0)
return - round(-x, p);
return ((long long)((x * pow10(p))) + 0.5) * pow10(-p);
}
回答by Greg
It doesn't for me on MySQL:
它不适合我在 MySQL 上：
mysql> select round(45.923,-1);
+------------------+
| round(45.923,-1) |
+------------------+
| 50 |
+------------------+
1 row in set (0.00 sec)
回答by Matt Howells
And on Sql Server 2005:
在 Sql Server 2005 上：
select round(45.923,-1)
------
50.000
What database are you running this on?
你在什么数据库上运行这个？
