MySQL#1140 - GROUP列的混合 [英] MySQL #1140 - Mixing of GROUP columns
问题描述
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节点
LEFT JOIN content_type_update node_data_field_update_date ON节点.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表示:
$ b
#1140 - 混合GROUP列(MIN(),MAX(),COUNT ),...)没有
GROUP列是非法的,如果有
没有GROUP BY子句的话
使用聚合函数 wo而没有使用聚合函数的列的版本不是因为你需要指定一个 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
我将表格别名更改为更短 - 更容易阅读。以下是您的问题:
SELECT n.nid,
COUNT(n.fake_example_column),
ctu.field_update_date_value
...
GROUP BY n.nid,ctu.field_update_date_value
为了突出GROUP BY需要如何定义,我将示例改为使用假列。您在 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 query: MySQL said: #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no
GROUP columns is illegal if there is
no GROUP BY clause`
The reason a single column using an aggregate function works while the version with columns not using aggregate functions doesn't is because you need to specify a I changed out your table aliases for shorter ones - easier to read. Here's the meat of your issue: 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 should to be mentioned in the 这篇关于MySQL#1140 - GROUP列的混合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋! GROUP BY
中提及的任何未引用聚合函数包装的列应。我说应该,因为 MySQL是我知道的唯一一个支持 GROUP BY
的数据库,您可以在其中选择性地忽略列 - 有很多关于为什么查询在MySQL上工作的问题但不能在不改变其他dbs的情况下移植。显然在你的情况下,你仍然需要定义至少一个。 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
GROUP BY
clause. Here's what your query should look resemble: 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
SELECT n.nid,
COUNT(n.fake_example_column),
ctu.field_update_date_value
...
GROUP BY n.nid, ctu.field_update_date_value
GROUP BY
. I say should because MySQL is the only db I'm aware of that supports a GROUP BY
where 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.