SELECT 列表不在 GROUP BY 子句中并且包含非聚合列 .... 与 sql_mode=only_full_group_by 不兼容 [英] SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

问题描述

AM 在我的带有 WAMP 服务器的 Windows PC 上使用 MySQL 5.7.13

AM using MySQL 5.7.13 on my windows PC with WAMP Server

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

Here my Problem is While executing this query

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

总是出现这样的错误

SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含在功能上不依赖于 GROUP BY 子句中的列的非聚合列returntr_prod.tbl_customer_pod_uploads.id"；这与 sql_mode=only_full_group_by 不兼容

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

你能告诉我最好的解决方案吗...

Can you please tell me the best solution...

我需要类似的结果

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

推荐答案

这个

SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含在功能上不依赖于 GROUP BY 子句中的列的非聚合列returntr_prod.tbl_customer_pod_uploads.id"；这与 sql_mode=only_full_group_by 不兼容

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

通过这个命令改变MySQL中的sql模式就可以解决了，

will be simply solved by changing the sql mode in MySQL by this command,

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

这对我也有用..我用了这个，因为在我的项目中有很多这样的查询所以我只是将这个sql模式改为only_full_group_by

This too works for me.. I used this, because in my project there are many Queries like this so I just changed this sql mode to only_full_group_by

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

OR simply include all columns in the GROUP BY clause that was specified by the SELECT statement. The sql_mode can be left enabled.

谢谢... :-)

这篇关于SELECT 列表不在 GROUP BY 子句中并且包含非聚合列 .... 与 sql_mode=only_full_group_by 不兼容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！