我用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 |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
下面的方法解决了我的问题:
在ubuntu中
类型:sudo vi /etc/mysql/my.cnf
输入A进入插入模式
在最后一行粘贴下面两行代码:
[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
按esc退出输入模式
Type :wq to save and close vim.
输入sudo service mysql restart重启mysql。
有时候你只需要检查你的查询是否有正确的Order和Order的方式。
GROUP BY pp.id_product
FIELD(pp.id_product, 1600, 1870, 2300 );
除非将排序方式(DESC或ASC)添加到查询中，否则该命令不会运行
GROUP BY pp.id_product
FIELD(pp.id_product, 1600, 1870, 2300 ) DESC;
这将在不破坏服务器的会话和全局设置的情况下完成任务。
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'， "));
和…记得重新连接数据库!!
MySQL 8.0升级
你的sql-mode不会有NO_AUTO_CREATE_USER，因为它已经被删除了，正如这里提到的-如何设置sql-mode-in-my-cnf-in-mysql-8
[mysqld]
sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
此外，如果有人没有my.cnf文件，那么他们可以在/etc/mysql/my.cnf中创建一个新的文件，然后添加上面的行。
在我的例子中，这是我的旧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';
