mysql group by 报错 ,only_full_group_by 三种解决方案
2024-09-27 13:05:38
报错信息
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.table.column' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
翻译过来就是在group by 的地方有一些列是没有包括进去的
具体问题具体分析,我这里的问题是:select 的时候使用了TIMESTAMPDIFF 中的两个列名并没有包括在group by 中
mysql 5.7以后的版本数据库的默认模式设置成了 only_full_group_by模式,而在执行的sql里有一些重复的行group by 的时候mysql 不知道选择哪一个行
错误实例
1:
SELECT
ifnull( sum( checkTime ) / count( 1 ), 0 ) AS time
FROM
( SELECT TIMESTAMPDIFF( MINUTE, CreateTime, EndTime ) AS checkTime FROM statistics_report WHERE AND ReportStatus = 2 GROUP BY reportid ) a
2:
SELECT
ifnull( sum( treatmentTime ) / count( 1 ), 0 ) AS time
FROM
(
SELECT
TIMESTAMPDIFF( MINUTE, min( CreateTime ), max( EndTime ) ) AS treatmentTime
FROM
statistics_report
WHERE
CreateTime > ? SartTime
AND CreateTime < ? EndTime
AND IsReportOperate = 2
AND createtime <> endtime
GROUP BY
reportid
) a
解决方案:
1:使用any_value() 包括具体提示的列名,使mysql 不再纠结与具体哪一个列
2: 把sql_mode 改成非only_full_group_by模式(如果是刚开发项目,且 ,对于项目有深入了解后 ,进行可行性分析后 再改,一般不建议,)
3:把sql 优化 ,对于每一个分组的元素 清晰的告知mysql 需要怎么选择
成功的写法:
1:
SELECT
ifnull( sum( checkTime ) / count( 1 ), 0 ) AS time
FROM
( SELECT TIMESTAMPDIFF( MINUTE, CreateTime, EndTime ) AS checkTime, reportid FROM statistics_report WHERE ReportStatus = 2 ) a
GROUP BY
reportid
-- 这里只是将group by 移动到了括号外面,在括号内不分组,在括号外进行分组,checkTime用sum函数来取值,解决问题。
2:
SELECT
ifnull( sum( timestampdiff( MINUTE, starttime, endtime ) ) / count( 1 ), 0 ) AS time
FROM
(
SELECT
min( createtime ) starttime,
max( endtime ) endtime,
reportid
FROM
statistics_report
WHERE
CreateTime > ? SartTime
AND CreateTime < ? EndTime
AND IsReportOperate = 2
AND createtime <> endtime
GROUP BY
reportid
) a
-- 这里是将timestampdiff 于min max 分开,在进行取最大最小值的时候group by ;在timestampdiff 的時候 不需要分組 ,因为这时候reportid 没有重复的。
-- 另外这里加了ifnull 是为了取到数据的时候避免null
结束分析:
group by 的问题 很奇怪的点在于 5.7 的mysql 版本改版;bug出现具有偶发性;在mysql client执行不抱错,而在程序中执行会报错;
这是哪个问题导致了问题的复杂性,在了解问题原理后 从根本上解决是最直接的方案。
时常问问自己:是不是太菜。
最新文章
- ubuntu14.04上Virtualbox安装win7(使用Ghost镜像安装,启用USB设备支持,设置共享目录)
- Html页面head标签元素的意义和应用场景
- Xperf Basics: Recording a Trace(转)
- IOS开发关于测试的好的网址资源
- MySQL并发调优和IO调优
- Bootstrap强调相关的类
- sql基础查询语句
- 使用XmlPullParser对xml进行读取
- 通过代码设置radiobutton不同方位图标的两种方法
- PHP函数十进制、二进制、八进制和十六进制转换
- 【中途相遇+二进制】【NEERC 2003】Jurassic Remains
- 讲解——Trie树(字典树)
- 【RL-TCPnet网络教程】第6章 RL-TCPnet底层驱动说明
- hbase的一些要点
- Exception、Thorow、Throws、TryCatch
- fiddler抓取手机上https数据失败,全部显示“Tunnel to......443”解决办法
- expdp和impdp 使用注意事项
- Redis进阶实践之六Redis Desktop Manager连接Windows和Linux系统上的Redis服务(转载6)
- 浅谈SDN架构下的运维工作
- Rust 阴阳谜题,及纯基于代码的分析与化简
热门文章
- js 的七大原则--单一原则、开闭原则、替换原则(一)
- Vivado Non-Project Flow
- hadoop完整集群遇到问题汇总
- Python学习日记(二十四) 继承
- (备忘)解决用Xftp向CentOS7 传文件速度慢的问题
- LB_Keogh
- python基础之面试常问
- selenium常用的API(三)获取网页title、html源码
- SpringBoot -生成Entity和Dto互转的双向枚举类 -使用注解@Mapper(componentModel = ";spring";)
- Spring-05 -AOP [面向切面编程] -Schema-based 实现aop的步骤