MySQL #1140 - GROUP 列的混合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1244169/
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 #1140 - Mixing of GROUP columns
提问by frosty
Hi wondering if perhaps someone could shed some light on the below error. The sql works fine locally but i get the the below error remotely.
嗨，想知道是否有人可以对以下错误有所了解。sql 在本地运行良好，但我远程收到以下错误。
SQL query:
SQL查询：
SELECT COUNT(node.nid),
node.nid AS nid,
node_data_field_update_date.field_update_date_value AS node_data_field_update_date_field_update_date_value
FROM node node
LEFT JOIN content_type_update node_data_field_update_date ON node.vid = node_data_field_update_date.vid
WHERE node.type IN ('update')
ORDER BY node_data_field_update_date_field_update_date_value DESC
MySQL said:
MySQL 说：
#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause`
#1140 - 如果没有 GROUP BY 子句，将 GROUP 列（MIN(),MAX(),COUNT(),...）与没有 GROUP 列的混合是非法的
采纳答案by OMG Ponies
The reason a single column using an aggregate functionworks while the version with columns not using aggregate functions doesn't is because you need to specify a GROUP BYclause. Here's what your query should look resemble:
使用聚合函数的单个列有效而具有不使用聚合函数的列的版本不起作用的原因是因为您需要指定一个GROUP BY子句。您的查询应如下所示：
SELECT COUNT(n.nid),
n.nid,
ctu.field_update_date_value
FROM NODE n
LEFT JOIN CONTENT_TYPE_UPDATE ctu ON ctu.vid = n.vid
WHERE n.type IN ('update')
GROUP BY n.nid, ctu.field_update_date_value
ORDER BY field_update_date_value DESC
I changed out your table aliases for shorter ones - easier to read. Here's the meat of your issue:
我将您的表别名更改为较短的别名 - 更易于阅读。这是你的问题的核心：
SELECT n.nid,
COUNT(n.fake_example_column),
ctu.field_update_date_value
...
GROUP BY n.nid, ctu.field_update_date_value
I altered the example to use a fake column in order to highlight how the GROUP BY needs to be defined. Any column you reference without wrapping in an aggregate function shouldto be mentioned in the GROUP BY. I say shouldbecause MySQL is the only db I'm aware of that supports a GROUP BYwhere you can selectively omit columns- there are numerous SO questions about why queries work on MySQL but can't be ported without change to other dbs. Apparently in your case, you still need to define at least one.
我修改了示例以使用假列，以突出显示 GROUP BY 需要如何定义。您引用的任何列都应在GROUP BY. 我说应该是因为MySQL 是我所知道的唯一一个支持GROUP BY你可以有选择地省略列的数据库- 有很多关于为什么查询在 MySQL 上工作但不能在不更改其他数据库的情况下移植的问题。显然，在您的情况下，您仍然需要至少定义一个。
回答by row1
Your server probably has ONLY_FULL_GROUP_BYturned on. You can either turn it off or add each of your selected fields to the group by.
您的服务器可能已打开ONLY_FULL_GROUP_BY。您可以将其关闭或将每个选定的字段添加到分组依据中。
回答by Waqas Bukhary
In Laravel Framework, setting 'strict' => false, in database file in the config folder will fix this.
在 Laravel Framework 中，'strict' => false在 config 文件夹中的数据库文件中设置, 将解决此问题。
回答by Jasim Juwel
when you use aggregate functions in sql like sum(),min(),max() you must be use group by
当您在 sql 中使用诸如 sum(),min(),max() 之类的聚合函数时，您必须使用 group by
But in latest sql u have to use all the columns in select as a group By too.
但是在最新的 sql 中，您也必须将 select 中的所有列作为一个组使用。
for this in laravel project you use in config->database
为此，您在 config->database 中使用的 Laravel 项目中
'strict' => false,
'严格' => 假，
and clear config cache by running php artisan config:cache.
并通过运行 php artisan config:cache 清除配置缓存。
i think it will be more helpfull for you.
我想这对你会更有帮助。
回答by Darkhan ZD
I have the same problem in old version of MySQL 5.0. In newer 5.5 version it works!
我在旧版本的 MySQL 5.0 中遇到了同样的问题。在较新的 5.5 版本中它有效！
SELECT COUNT(*), id
FROM `cities` AS `c`
回答by frosty
stripped it back to basics:
将其剥离回基础：
SELECT COUNT( node.nid )
FROM node node
LEFT JOIN content_type_update node_data_field_update_date ON node.vid = node_data_field_update_date.vid
WHERE node.type IN ('update')
ORDER BY node_data_field_update_date.field_update_date_value DESC
