我在一个网站上工作,该网站要求我显示用户每天输入的平均数量的图表。我已经有一个SQL查询将此信息返回给我:
SELECT sum(number)/count(number) as average, date FROM stats WHERE * GROUP BY date
这给出了我想要的结果,但结果是三位小数精度的。我想对这个数字进行四舍五入。当然,我可以用PHP或我的模板引擎来完成,但我很好奇是否有一种方法可以在数据库中完成所有这些工作。
有没有办法将输出转换为整数(在MySQL中)?
发布于 2012-01-17 22:07:41
SELECT
CAST(sum(number)/count(number) as UNSIGNED) as average,
date
FROM stats
WHERE *
GROUP BY date
发布于 2012-01-17 22:07:32
使用MySQL FORMAT
函数怎么样?
mysql> SELECT FORMAT(12345.123456, 4);
+-------------------------+
| FORMAT(12345.123456, 4) |
+-------------------------+
| 12,345.1235 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT FORMAT(12345.123456, 0);
+-------------------------+
| FORMAT(12345.123456, 0) |
+-------------------------+
| 12,345 |
+-------------------------+
1 row in set (0.00 sec)
发布于 2012-01-17 22:07:21
SELECT convert(int, sum(number)/count(number)) as average,
date
FROM stats
WHERE * GROUP BY date
或
SELECT
CAST(sum(number)/count(number) as INT) as average,
date
FROM stats
WHERE *
GROUP BY date
https://stackoverflow.com/questions/8895927
复制相似问题