MySQL #1140 - GROUP 列的混合

2021-11-20 00:00:00 sql group-by mysql mysql-error-1140

想知道是否有人可以对以下错误有所了解.sql 在本地运行良好，但我远程收到以下错误.

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 查询:

   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 说:

#1140 - 混合 GROUP 列(MIN(),MAX(),COUNT(),...)GROUP 列是非法的，如果有没有 GROUP BY 子句`

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause`

推荐答案

单列使用 聚合函数 起作用，而列不使用聚合函数的版本不起作用，因为您需要指定 GROUP BY 子句.您的查询应如下所示:

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 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

我将您的表别名更改为较短的别名 - 更易于阅读.这是您的问题的核心:

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

我修改了示例以使用假列，以突出需要如何定义 GROUP BY.您引用的任何列都应该在GROUP BY 中提及而没有包含在聚合函数中应该被提及.我说应该因为MySQL 是我所知道的唯一一个支持 GROUP BY 的数据库，您可以在其中选择性地省略列 - 关于为什么查询在 MySQL 上工作但不能移植的问题有很多无需更改其他数据库.显然，在您的情况下，您仍然需要至少定义一个.

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 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.

相关文章