1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘tab

最新推荐文章于 2025-03-06 21:09:40 发布
原创 最新推荐文章于 2025-03-06 21:09:40 发布 · 2.6k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文详细介绍了在使用MySQL时遇到的only_full_group_by错误，该错误源于SQL语句中GROUP BY子句的不当使用。文章提供了两种解决方案：一是临时修改SQL模式，二是永久更改MySQL配置文件my.ini中的sql_mode设置，以避免未来出现类似问题。

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

执行sql语句报错：

SELECT list的表达式#1不在GROUP BY子句中，它包含非聚合列’table1 '。sale_id’在功能上不依赖于GROUP BY子句中的列;这与sql_mode=only_full_group_by不兼容

解决办法：

在mysql中输入

mysql> set sql_mode =‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;

或
在mysql的安装目录下找到其配置文件my.ini，然后在其最底部添加如下配置：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

保存文件，重启mysql服务

再次从新查询就OK了！

Spark-SQL那些事
zhixingheyi_tian的博客
05-18 1494
WITH AS WITH AS短语，也叫做子查询部分，定义一个SQL片断后，该SQL片断可以被整个SQL语句所用到。有的时候，with as是为了提高SQL语句的可读性，减少嵌套冗余。 with A as ( select * from user ) select * from A, customer where customer.userid = user.id** 先执行select * from user把结果放到一个临时表A中，作为全局使用。 with as的将频繁执行的s
MySql遇到的问题--group by报错“1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains..”
Dbao_miao的博客
03-20 1911
学习group by遇到的问题表，id为主键 表，id为主键 ±—±---------±----------±--------------------±-----------+ | id | LastName | FirstName | Address | City | ±—±---------±----------±--------------------±-----------+ | 1 | Adams | John | Oxford Street
MySQL报错“Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”解决方法
lucky多多的博客
03-22 5万+
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
MySQL报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column whic
weixin_63716356的博客
07-24 2195
MySQL报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column whic字面翻译： SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列“grades.order_id” 它在功能上不依赖于 GROUP BY 子句中的列； 这与 sql_mode=only_full_group_by 不兼容使用GROUP BY 语句违背了 sql_mode=only_
Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
李利科
02-01 3693
执行以下sql报错： SELECT name,sex,location FROM tp_music.singer group by sex; 报错信息： Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘tp_music.singer.name’ which is not functionally dependent on columns i
mysql报错解决： 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
危险、的博客
01-25 4106
报错如下： Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘sss.month_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 问题出现的原因： MySQL 5.7.5及以上功
SELECT tqp.id, tqp.patient_id AS patientId, tqp.doctor_id AS doctorId, tqp.queue_priority, tqp.sort_index AS `index`, tqp.department, tqp.status, su.realname, su.interval_time, sd.depart_name AS departName, p.name AS patientName, p.gender AS patientGender, p.date_birth AS dateBirth, tqp.reservation_id AS reservationId, tqp.room AS roomId FROM tab_queue_patient tqp LEFT JOIN patients p ON tqp.patient_id = p.id LEFT JOIN sys_user su ON tqp.doctor_id = su.id LEFT JOIN sys_depart sd ON tqp.department = sd.id WHERE tqp.status != 4 AND tqp.check_time BETWEEN (NOW() - INTERVAL 18 HOUR) AND (NOW() + INTERVAL 6 HOUR) AND tqp.status = ? AND tqp.pkpi = ? GROUP BY tqp.doctor_id, tqp.pkpi ORDER BY tqp.call_time DESC\r\n### Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'queue_gjyx.tqp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\n; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'queue_gjyx.tqp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
最新发布
11-19
我们遇到的问题是在使用GROUP BY时，由于MySQL的sql_mode包含only_full_group_by，导致SELECT列表中的某些列（非聚合列且不在GROUP BY子句中）引发错误。 根据引用[5]，MySQL 5.7及以上版本默认启用ONLY_FULL_GROUP...
【MySQL-SQL执行顺序】MySQL中SQL的执行顺序和常见问题
weixin_53543905的博客
02-26 1632
那么找name那一列，具有相同name值的行，合并成一行，如对于name值为aa的，那么与两行合并成1行，所有的 id 值和 number 值写到一个单元格里面。（6）接下来就可以配合select和聚合函数进行操作了。的话，那么返回的结果应该是虚拟表3，可是id和number中有的单元格里面的内容是多个值的，而关系数据库就是基于关系的，单元格中是不允许有多个值的，所以执行。是对分组（group by）后的筛选条件，对分组后的数据进行筛选，作用于每个组，限制的是组，而不是行。
MySQL-8.0.13修改sql_mode后无法进入数据库，重装都不管用
qq_43242305的博客
11-19 2607
MySQL-8.0.13修改sql_mode后重装都无法进入数据库 我使用的mysql-8.0.13-winx64版本的数据库，我在使用分组查询时候遇到了问题， Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre 然后就按照网上说的修改了sql_mode，也就是把ONLY_FULL_GROUP_B...
Elasticsearch6.4专题之17:SQL Access
天空之蓝的博客
11-10 767
SQL Access 文章目录SQL Access概述简介为什么选择Elasticsearch SQL本机集成没有外部零件轻巧高效入门Conventions and Terminology（约定和术语）Mapping concepts across SQL and Elasticsearch（SQL和ES的概念映射）SecuritySQL REST APISQL Translate APISQL ...
mysql报错 ＞ 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
weixin_44013783的博客
08-05 1万+
数据库查询时报错 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘dkia_quality_db.b.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 先说产生这个
mysql5.7 报错1055:Expression #1 of SELECT list is not in GROUP BY clause and contains non
优快云_ljm的博客
11-07 4126
（参考：https://blog.youkuaiyun.com/qq_24038207/article/details/79358644）经尝试未成功！！！ 在上面修改的基础上，本人成功的解决方法， cd /etc/mysql/mysql.conf.d/     （有的mysql配置文件是 /etc/my.cnf ，而我的Ubuntu16.04 + mysql 5.7.24 下 mysql配置文件是这里...
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.
热门推荐
fansili的博客
11-29 8万+
安装了mysql5.7，用group by 查询时抛出如下异常：Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.t_long.user_name' which is not functionally dependent on columns in GR
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘se
m0_61615803的博客
04-11 7413
模式，即在 GROUP BY 语句中的 SELECT 列表中，只能包含分组或聚合函数，不能包含其他列。而你的查询语句中出现了一个列。列加入 GROUP BY 列表中，这样 MySQL 就能知道如何处理这个列了。这个查询将按学生 ID 分组，并计算每个学生的平均分。这样 MySQL 就不会报出上述错误了。，它既没有被分组也没有被聚合，因此 MySQL 报出了这个错误。这个错误是由于 MySQL 的新版本中默认开启了。Navicat Premium 16 版本。
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column报错
每天进步一点点...
11-17 1999
在MySQL语法GroupBy时报错 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db1.product.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by,
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains
Mr_yiyang的博客
04-28 599
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xschool.sc_user_message.CREATED_USER’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
MySQL 报错: “Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column“
总结复盘，才能不迷失自己！
03-06 1017
1.问题描述MySQL 中出现 “1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column” 错误？
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cre
weixin_43085797的博客
04-14 1万+
sql语句 SELECT * FROM t_company_change_log WHERE company_id='257949626441666560' AND rec_status='N' GROUP BY change_item 这样会在某些版本的数据库中运行出错 原因是 5.7.5之后版本默认启用了only_full_group_by SQL模式，所以MySQL会不识别选择列表...
[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated colum
小志的博客
10-25 1万+
[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated colum
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
03-29
1. 将SELECT列表中的所有列都添加到GROUP BY子句中。这样可以确保所有的列都被正确地分组。 2. 如果你只想要对某些列进行分组，而不是所有的列，可以使用聚合函数对其他列进行处理。例如，使用SUM、COUNT、MAX等聚合...
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值