MySQL : 不在 GROUP BY 中
该站点产生结果，但 SELECT COUNT 和 SELECT 查询与 GROUP BY 有两个不同的结果计数.这可能是由于 phpmyadmin 中显示的错误，而不是网站上显示的错误.
The site produces results, but with SELECT COUNT and SELECT query with GROUP BY having two different result counts. This is likely due to the error that is displaying in phpmyadmin but not on the site.
查询:
SELECT count(DISTINCT `name`) as `numrows` FROM `users` WHERE `verified` = '1'
SELECT `name`, `type`, `language`, `code` FROM `users` WHERE `verified` = '1' GROUP BY `name` ORDER BY `count` DESC LIMIT 0, 25
PhpMyAdmin 提供以下错误:
PhpMyAdmin provides the following error:
1055 - 'main.users.type' 不在 GROUP BY 中
1055 - 'main.users.type' isn't in GROUP BY
在阅读 MySQL 文档时，我仍然不清楚我必须修复什么.我似乎无法理解这一点.
When reading MySQL docs, I'm still unclear what it is I have to fix. I can't seem to grasp this.
推荐答案
您需要有一个完整的组:
You need to have a full group by:
SELECT `name`, `type`, `language`, `code`
FROM `users`
WHERE `verified` = '1'
GROUP BY `name`, `type`, `language`, `code`
ORDER BY `count` DESC LIMIT 0, 25
SQL92 要求 select 子句中的所有列(聚合除外)都是 group by 子句的一部分.SQL99 稍微放宽了这个限制，并声明 select 子句中的所有列必须在功能上依赖于 group by 子句.MySQL 默认允许部分分组，这可能会产生不确定的答案，例如:
SQL92 requires that all columns (except aggregates) in the select clause is part of the group by clause. SQL99 loosens this restriction a bit and states that all columns in the select clause must be functionally dependent of the group by clause. MySQL by default allows for partial group by and this may produce non-deterministic answers, example:
create table t (x int, y int);
insert into t (x,y) values (1,1),(1,2),(1,3);
select x,y from t group by x;
+------+------+
| x | y |
+------+------+
| 1 | 1 |
+------+------+
即为组 x 选择随机 y.可以通过设置@@sql_mode 来防止这种行为:
I.e. a random y is select for the group x. One can prevent this behavior by setting @@sql_mode:
set @@sql_mode='ONLY_FULL_GROUP_BY';
select x,y from t group by x;
ERROR 1055 (42000): 'test.t.y' isn't in GROUP BY
相关文章