MySQL:不在GROUP BY中

本文介绍了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