MySQL 在 SQL 中选择/转换输出为整数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8895927/
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
Selecting/casting output as integer in SQL
提问by MrGlass
I'm working on a site that requires me to display a graph of the average number per day of a user input. I have a SQL query already that returns this info to me:
我在一个网站上工作，该网站要求我显示每天用户输入的平均数量的图表。我已经有一个 SQL 查询可以返回此信息给我：
SELECT sum(number)/count(number) as average, date FROM stats WHERE * GROUP BY date
This gives me the result I am looking for, but the result is given with three decimals precision. I want to round of this number. I could do it in PHP or my template engine, of course, but I was curious if there was a way to do this all in the database.
这给了我我正在寻找的结果，但结果以三位小数精度给出。我想四舍五入这个数字。当然，我可以在 PHP 或我的模板引擎中完成，但我很好奇是否有办法在数据库中完成这一切。
Is there a way to cast an output as an integer (in MySQL)?
有没有办法将输出转换为整数（在 MySQL 中）？
回答by Oleg Dok
SELECT
CAST(sum(number)/count(number) as UNSIGNED) as average,
date
FROM stats
WHERE *
GROUP BY date
回答by Martin Smith
The valid types for a CASTin MySQL are as follows
CASTMySQL中a的有效类型如下
BINARY[(N)]CHAR[(N)]DATEDATETIMEDECIMAL[(M[,D])]SIGNED [INTEGER]TIMEUNSIGNED [INTEGER]
BINARY[(N)]CHAR[(N)]DATEDATETIMEDECIMAL[(M[,D])]SIGNED [INTEGER]TIMEUNSIGNED [INTEGER]
So you could use
所以你可以使用
SELECT CAST(sum(number)/count(number) AS UNSIGNED) as average...
Or SIGNEDif the SUMpart can ever add up to a negative number.
或者，SIGNED如果SUM零件可以加起来为负数。
回答by djechlin
Use the DIVoperator.
使用DIV运算符。
mysql> SELECT 5 DIV 2;
-> 2
Integer division. Similar to FLOOR(), but is safe with BIGINT values. Incorrect results may occur for noninteger operands that exceed BIGINT range.
整数除法。类似于 FLOOR()，但对于 BIGINT 值是安全的。对于超出 BIGINT 范围的非整数操作数，可能会出现不正确的结果。
回答by John Woo
how about using MySQL FORMATFunction?
如何使用 MySQLFORMAT函数？
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)
回答by aF.
SELECT convert(int, sum(number)/count(number)) as average,
date
FROM stats
WHERE * GROUP BY date
or
或者
SELECT
CAST(sum(number)/count(number) as INT) as average,
date
FROM stats
WHERE *
GROUP BY date
