MySQL : 不在 GROUP BY 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25800411/
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
MySQL : isn't in GROUP BY
提问by James Cordeiro
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 和 SELECT 查询与 GROUP BY 有两个不同的结果计数。这可能是由于 phpmyadmin 中显示的错误，而不是网站上显示的错误。
The Queries:
查询：
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 provides the following error:
PhpMyAdmin 提供以下错误：
1055 - 'main.users.type' isn't in GROUP BY
1055 -“main.users.type”不在 GROUP BY 中
When reading MySQL docs, I'm still unclear what it is I have to fix. I can't seem to grasp this.
在阅读 MySQL 文档时，我仍然不清楚我必须修复什么。我似乎无法理解这一点。
回答by Lennart
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 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:
SQL92 要求 select 子句中的所有列（聚合除外）都是 group by 子句的一部分。SQL99 稍微放宽了这个限制，并声明 select 子句中的所有列必须在功能上依赖于 group by 子句。MySQL 默认允许部分分组，这可能会产生不确定的答案，例如：
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 |
+------+------+
I.e. a random y is select for the group x. One can prevent this behavior by setting @@sql_mode:
即为组 x 选择随机 y。可以通过设置@@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
回答by O. Jones
The best solution to this problem is, of course, using a complete GROUP BYexpression.
这个问题的最佳解决方案当然是使用完整的GROUP BY表达式。
But there's another solution that works around the ONLY_FULL_GROUP_BYblocking of the old MySQL extension to GROUP BY.
但是还有另一种解决方案可以解决ONLY_FULL_GROUP_BY将旧的 MySQL 扩展阻止到GROUP BY.
SELECT name,
ANY_VALUE(type) type,
ANY_VALUE(language) language,
ANY_VALUE(code) code
FROM users
WHERE verified = '1'
GROUP BY name
ORDER BY count DESC LIMIT 0, 25
ANY_VALUE()explicitly declares what used to be implicit in MySQL's incomplete GROUP BYoperations -- that the server can choose, well, any, value to return.
ANY_VALUE()显式声明 MySQL 不完整GROUP BY操作中曾经隐含的内容——服务器可以选择任何值来返回。
回答by Juri Sinitson
Another solution mentioned multiple times above is to turn off that annoying 'ONLY_FULL_GROUP_BY'e.g. like in this post: Disable ONLY_FULL_GROUP_BY
上面多次提到的另一个解决方案是关闭烦人的“ONLY_FULL_GROUP_BY”，例如在这篇文章中： 禁用 ONLY_FULL_GROUP_BY
I think this solution is very useful if you do not want to refactor the whole project for multiple hours. And if you do not care about the unpredictable values of the columns which are not list of the GROUP BY.
如果您不想在多个小时内重构整个项目，我认为此解决方案非常有用。如果您不关心不是GROUP BY列表的列的不可预测值。
回答by kjdion84
Just disable strictness for the query.
只需禁用查询的严格性。
回答by Szél Lajos
First thing is to see why it is like that. Earlier versions allowed us to be negligent a bit: while the values of the grouped column are completely listed (one from each without exceptions) in the result, values of the other selected column(s) are more or less omitted - but the code can't (does not want to) tell which ones on your behalf:
第一件事是看看为什么会这样。早期版本允许我们疏忽一点：虽然分组列的值在结果中完全列出（每个列一个，没有例外），其他选定列的值或多或少被省略 - 但代码可以't（不想）代表你说出哪些：
- Hey, MYSQL, list the families of the street - but add a first name as well to every row.
- Okay sir, but shall I use the father's or the mother's or...? I'm a machine, give exact orders!
Now that we understand the why, we can decide if we have preferences regarding the other column(s). Use
现在我们了解了原因，我们可以决定是否对其他列有偏好。用
MAX() AS
MIN() AS
etc, works with strings, too
or if it's really all the same, e.g. there's no difference between the nonaggregated values that are related to the grouped one, use
或者如果它真的完全相同，例如与分组值相关的非聚合值之间没有区别，请使用
ANY_VALUE() AS
Either way, you let mysql know that you're aware of being "equivocal" but you really don't want to focus on all the columns other than the one(s) you grouped.
无论哪种方式，您都让 mysql 知道您知道“模棱两可”，但您真的不想关注除您分组的列之外的所有列。
