If you name columns to select in addition to the COUNT()value, a GROUP BYclause should be present
that names those same columns. Otherwise, the following occurs:
? If the ONLY_FULL_GROUP_BYSQL mode is enabled, an error occurs:
mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT name, COUNT(*) FROM test;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause
? If ONLY_FULL_GROUP_BYis not enabled, the query is processed by treating all rows as a single group,
but the value selected for each named column is indeterminate. The server is free to select the value
from any row:
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT name, COUNT(*) FROM test;
+-------+----------+
| name | count(*) |
+-------+----------+
| jason | 2 |
+-------+----------+
1 row in set (0.00 sec)
如果设置sql_mode参数为空，不启用ONLY_FULL_GROUP_BY，则统计数量时，不加group by ，语句不报错。
但是，其他实体列的值，没有分组，只是取表中默认排序的第一行数据。
通过下面查询语句可以看出：
that names those same columns. Otherwise, the following occurs:
? If the ONLY_FULL_GROUP_BYSQL mode is enabled, an error occurs:
mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT name, COUNT(*) FROM test;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause
? If ONLY_FULL_GROUP_BYis not enabled, the query is processed by treating all rows as a single group,
but the value selected for each named column is indeterminate. The server is free to select the value
from any row:
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT name, COUNT(*) FROM test;
+-------+----------+
| name | count(*) |
+-------+----------+
| jason | 2 |
+-------+----------+
1 row in set (0.00 sec)
如果设置sql_mode参数为空，不启用ONLY_FULL_GROUP_BY，则统计数量时，不加group by ，语句不报错。
但是，其他实体列的值，没有分组，只是取表中默认排序的第一行数据。
通过下面查询语句可以看出：
mysql> select name,count(*) from test group by name;
+-------+----------+
| name | count(*) |
+-------+----------+
| jack | 1 |
| jason | 1 |
+-------+----------+
2 rows in set (0.00 sec)
mysql> select * from test;
+------+-------+------------+
| id | name | birthday |
+------+-------+------------+
| 1 | jason | 2015-01-21 |
| 2 | jack | 2015-01-21 |
+------+-------+------------+
2 rows in set (0.00 sec)
+-------+----------+
| name | count(*) |
+-------+----------+
| jack | 1 |
| jason | 1 |
+-------+----------+
2 rows in set (0.00 sec)
mysql> select * from test;
+------+-------+------------+
| id | name | birthday |
+------+-------+------------+
| 1 | jason | 2015-01-21 |
| 2 | jack | 2015-01-21 |
+------+-------+------------+
2 rows in set (0.00 sec)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29193965/viewspace-1411165/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
oracle11gRAC安装问题汇总
下一篇：
open_files_limit 含义
请登录后发表评论
登录
全部评论
