MySQL : 不在 GROUP BY 中 [英] MySQL : isn't in 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

这篇关于MySQL : 不在 GROUP BY 中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！