[MySQL] group by 聚合函数的原理和聚合限制原因SELECT list is not in GROUP BY clause and contains nonaggregated colu...

最新推荐文章于 2023-11-07 22:40:00 发布
最新推荐文章于 2023-11-07 22:40:00 发布
阅读量256 收藏 1
点赞数
文章标签： java 数据库 mysql sql 面试
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/taoshihan/article/details/116187220
版权

group by 的原理是 

先把数据根据分组字段 , 分好组 ,

然后再每个分组里面执行具体的聚合操作

ONLY_FULL_GROUP_BY模式的限制是:

SELECT list is not in GROUP BY clause and contains nonaggregated column

select查询的列里面 , 有不是group by子句中的字段 , 有不是聚合函数的字段

原因是:

group by后 , 所有的字段和属性 , 都是这个分组的属性了

一个小组的属性里 , 只有知道 最大 / 最小 / 平均 /  总数  , 这样的聚合属性才是有意义的

每个组员个体的属性是没有意义的 

因此才会出现这个限制

程序员老狼
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql 8.0下的SELECT list is not in GROUP BY clause and contains nonaggregated column
aituochang1886的博客
08-27 1059
mysql的版本 mysql> select version();+-----------+| version() |+-----------+| 8.0.12 |+-----------+ 在执行group by时遇到报错，具体如下 mysql> select * from api_properties GROUP BY file_id order by fi...
mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column...原因及解决方案
江海寄余生的博客
03-30 1万+
最近公司新建了一个项目，在项目中需要用到group by进行聚合计算，在计算的同时也要查出一些其他字段来返回给前端。于是就有了这个错误的出现。 先简单复现我所写的sql，其实sql非常简单。 select channel_name as channelName, brand_name as brandName, sum(actual_value) as actualValue, sum(actual_value_ty) a
group by 分析其容易出现BUG详解。（SELECT list is not in GROUP BY clause and contains nonaggregated column）
qq_43660506的博客
04-06 522
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shyk.t_eqm_sbjd_ndsbjd.LastModifiedDateTime' which is ***not functionally dependent on columns in GROUP BY clause***; this is incompatible with sql_mode=only_full_gro
GROUP BY clause and contains nonaggregated 报错处理
noya202020的博客
08-05 1385
1055 - Expression #16 of SELECT list is not in GROUP BY clause and contains nonaggregated column 报错处理 源码想移植到新的机器，MySQL 使用PHPStudy 安装 升级到5.7.26 。 一切准备就绪但是，出现以上错误。 话不多说解决办法， 1、主要原因 Mysql默认 的 sql_mode 默认 ONLY_FULL_GROUP_BY 值，需要把它去掉。 2 去掉办法 尝试很多种办法，比如 MySQL&g
MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法
最新发布
m0_67882861的博客
11-07 3325
因为sno属性是主码，即primary key，经测试发现group by **，**为主码的属性名时，select语句中的查询列没有在group by中出现，也是允许的。红色部分可以发现sc.sno与studen.sno都没有在GROUP BY的列中，但执行语句依旧正常，因为使用了聚集函数。执行mysql语句，select 选择的列不仅仅只有sno，为什么正常输出？存书（书号，书名，出版社，版次，出版日期，作者，书价，进价，数量）现有两表Student表以及SC表(选课表)。student表结构。
mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column...解决方案
热门推荐
study_in的博客
06-19 7万+
MySQL5.7.5后only_full_group_by成为sql_mode的默认选项之一，这可能导致一些sql语句失效。 比如在使用group by进行分组查询报错 1 查看自己的sql_mode配置: 在sql命令行中输入select @@sql_mode;这时我们能够看到自己的sql_mode配置,其中如果有ONLY_FULL_GROUP_BY,那它就是group by查询报错的罪魁祸...
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
12-15
如果启用了only_full_group_by SQL模式(在默认情况下是这样)，那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。(在5.7.5之前，MySQL没有检测...
MySQL异常:[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nona…
12-14
[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not functionally dependent on columns in GROUP ...
mysql遇到Expression.docx
03-05
在MySQL中，当你尝试执行一个包含`GROUP BY`子句的查询时，可能会遇到"Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column"这样的错误。这个错误提示表明你的查询违反了`...
Mysql升级到5.7后遇到的group by查询问题解决
12-16
发现问题 最近在将mysql升级到mysql 5.7后，进行一些group by...SELECT list is not in GROUP BY clause and contains nonaggregated column ‘news.id' which is not functionally dependent on columns in GROUP BY c
Mysql解决SELECT list is not in GROUP BY clause and contains nonaggregated column 问题
皮蛋瘦肉的博客
03-15 5万+
在使用GROUP BY对Mysql的数据表进行查询时如果出现以下错误 select * from user group by age; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.user.user_...
mysql 8版本出现 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggreg...
qq_42851623的博客
10-28 1453
mysql 8版本出现 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggreg...
SELECT list is not in GROUP BY clause and contains nonaggregated column
J_bean的博客
03-07 7669
报错 MySQL 8.0.22 GROUP BY 语句执行报错： 1055 - Expression #22 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx.xxx.xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_fu
#1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this i
大道至简，持之以恒！
01-11 9399
#1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this i
[MySQL]SELECT list is not in GROUP BY clause and contains nonaggregated column...
焚忆肆寒的博客
10-13 3442
报错详情：（sql版本8.0.13） SELECT list is not in GROUP BY clause and contains nonaggregated column '***' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ... 项目在windows上运行正常，迁移到...
MySQL错误SELECT list is not in GROUP BY clause and contains nonaggregated column解决”
宋发元
06-21 529
错误信息 Expression #12 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘amazon_admin.r.role_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 解决办法 用vim打开m
mongodb利用aggregate聚合group by多个字段
fuck487的博客
04-08 2万+
转载：https://blog.csdn.net/s630405377/article/details/78478981 var result = db.orderMain.aggregate([ {$match:{ partnerId:{$eq:"aaa"} }}, {$group:{ _id:{ partnerId:"$partnerId", clientCode:"$shipper.cl...
MySQL: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sss
蚁人的博客
03-21 2万+
执行MySQL中sql语句的时候报错，执行的sql语句：SELECT prov_desc, area_desc, month_id, MAX(total_fee)AS max_total, FROM sss WHERE prov_id = '075' OR prov_id IN('017') AND month_id IN('201207') GROU
完美解决java.sql.SQLException: Column ' XXX ' not found.
宜春
06-14 5万+
最近做javaWeb项目，出现了一个小插曲java.sql.SQLException: Column ’ name ’ not found，首先分析出现小插曲的本质问题： 1、查询语句是否有问题 2、是否有name 3、或者就是实体映射问题,实体类没有name 就拿我当时的小插曲做个简单分析喽！ 首先我的查询语句：findRownumberMax="SELECT MAX(rownumbername...
mysql5.7.34使用group by不规范，导致SELECT list is not in GROUP BY clause and contains nonaggregated column
10-13
这个问题是因为 MySQL 5.7.5 之后的版本默认启用了 ONLY_FULL_GROUP_BY 模式，要求 GROUP BY 子句中的列必须出现在 SELECT 列表中或者使用聚合函数进行处理。如果不想启用 ONLY_FULL_GROUP_BY 模式，可以在配置文件...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值