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

当前回答

This

将通过这个命令改变MySQL中的sql模式来简单地解决，

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

这也适用于我。我使用这个，因为在我的项目中有很多这样的查询，所以我只是将这个sql模式更改为only_full_group_by

或者简单地包括SELECT语句指定的GROUP BY子句中的所有列。sql_mode可以保持启用状态。

谢谢你！: -)

2017-01-27 05:44:32

其他回答

Hi不是获取所有列，而是使用ANY_VALUE(column_name)获取所需的列。它工作得很完美。只是检查。

例如:

SELECT proof_type,any_value("customer_name") as customer_name
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND `status` = 'Active' GROUP BY `proof_type`

2020-01-31 14:01:49

我正面临着这个问题，下面的问题还不够

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

因为我用的是存储过程。执行此查询后，我必须删除过程并重新创建。

2021-03-24 07:05:21

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

如果你正在使用PhpMyAdmin，搜索“SQL模式”，然后去掉值:ONLY_FULL_GROUP_BY, just did and it ok。

2020-02-03 07:42:23

在我的例子中，这是我的旧sql_mode

sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

您可以使用

show variables like "sql_mode";

然后我把它设置成这样

SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

2022-04-21 15:26:24

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

推荐文章

最新文章

标签