SELECT 列表不在 GROUP BY 子句中并且包含非聚合列......与 sql_mode=only_full_group_by 不兼容
在带有 WAMP 服务器的 Windows PC 上使用 MySQL 5.7.13 的 AM
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 子句中，并且包含非聚合列returntr_prod.tbl_customer_pod_uploads.id"，该列在功能上不依赖于 GROUP BY 子句中的列；这与 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 子句中，并且包含非聚合列returntr_prod.tbl_customer_pod_uploads.id"，该列在功能上不依赖于 GROUP BY 子句中的列；这与 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
OR 仅包含 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.
谢谢... :-)
相关文章