SELECT列表不在GROUP BY子句中，包含未聚合的列....sql_mode=only_full_group_by不兼容

我用WAMP服务器在我的windows PC上使用MySQL 5.7.13

我的问题是在执行这个查询时

SELECT *
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND
      `status` = 'Active'
GROUP BY `proof_type`

我总是得到这样的错误

SELECT列表中的表达式#1不在GROUP BY子句中，包含未聚合的列returntr_prod.tbl_customer_pod_uploads。id'，它不依赖于GROUP BY子句中的列;这与sql_mode=only_full_group_by不兼容

你能告诉我最好的解决办法吗?

我需要这样的结果

+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
| id | user_id | load_id | bill_id | latitude | langitude | proof_type | document_type | file_name    | is_private | status | createdon           | updatedon           |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
|  1 |       1 | 78      | 1       | 21.1212  | 21.5454   |          1 |             1 | id_Card.docx |          0 | Active | 2017-01-27 11:30:11 | 2017-01-27 11:30:14 |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+

当前回答

如果你在Mac上使用MAMP，当你重新启动MAMP时，对MySQL变量的任何更改都将被擦除。要确保更改是永久性的，请执行以下操作:

停止正在运行的MAMP服务器。

使用文本编辑器创建一个名为my.cnf的文件，并将其保存到/Applications/MAMP/conf文件夹中。将文件放在文件夹的根目录下(这看起来有点奇怪，因为它包含了大量的apache文件夹，但这是可以的)。在文件中添加如下代码行:

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

保存文件并关闭编辑器。启动MAMP服务器。

这将使改变成为永久性的。

2022-01-06 14:08:53

其他回答

如果你在Mac上使用MAMP，当你重新启动MAMP时，对MySQL变量的任何更改都将被擦除。要确保更改是永久性的，请执行以下操作:

停止正在运行的MAMP服务器。

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

保存文件并关闭编辑器。启动MAMP服务器。

这将使改变成为永久性的。

2022-01-06 14:08:53

在运行查询之前运行下面的查询

SET SQL_MODE = '';

2021-05-02 13:42:27

For the query to be legal in SQL92, the name column must be omitted from the select list or named in the GROUP BY clause. SQL99 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns: If such a relationship exists between name and custid, the query is legal. This would be the case, for example, were custid a primary key of customers. MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. via MySQL :: MySQL 5.7 Reference Manual :: 12.19.3 MySQL Handling of GROUP BY

你可以用下面的命令改变sql模式来解决这个问题:

SET GLOBAL sql_mode=(SELECT (@@sql_mode，'ONLY_FULL_GROUP_BY'， "));

和…记得重新连接数据库!!

2017-10-19 07:58:07