带有ROUND函数的mysql avg函数

Question

我用过的mysql查询

avg(column_name)  

返回值 12.50 但如果我像 avg 函数那样舍入

ROUND(avg(column_name))

然后它返回值 12 但它必须是 13。我不知道为什么它返回 12 而不是 13

如果我执行 ROUND(12.50)，它会返回值“13”和 avg(column_name)，即 12.50。当我执行 ROUND(avg(column_name)) 时返回值“12”。我认为在这两种情况下输出都需要是“13”。

Answer 1

http://dev.mysql.com/doc/refman/5.0/en/precision-math-rounding.html :

As of MySQL 5.0.3, ROUND() uses the following rules depending on the type of the first argument:

For exact-value numbers, ROUND() uses the “round half up” or “round toward nearest” rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.

For approximate-value numbers, the result depends on the C library. On many systems, this means that ROUND() uses the "round to nearest even" rule: A value with any fractional part is rounded to the nearest even integer.

The following example shows how rounding differs for exact and approximate values:

mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3          |            2 |
+------------+--------------+