SELECT 列表不在 GROUP BY 子句中并且包含非聚合列 .... 与 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.
谢谢... :-)
相关文章