代码编织梦想

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘smart_site.tc.company_name’; this is incompatible with sql_mode=only_full_group_by

原因:这个错误是由于MySQL的"ONLY_FULL_GROUP_BY" SQL模式导致的。在这种模式下,当使用聚合函数(如SUM、COUNT、MAX等)时,SELECT列表中的列必须要么是聚合函数的参数,要么包含在GROUP BY子句中。
这种一般有几种解决办法

1.修改数据库sql模式

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); 
set  @@global.sql_mode=(SELECT REPLACE(@@global.sql_mode,'ONLY_FULL_GROUP_BY','')); 

2.使用any_value() 函数

SELECT any_value(tc.company_name  )                                               as companyName,
               tp.project_name                                                 as projectName,
               tp.project_leader_id                                            as projectLeaderId,
               count(distinct (case when tw.worker_status = 1 then tw.id end)) as workerNum,
               count(distinct (twa.worker_id))                                 as attendanceNum
        FROM  t_project tp
                 LEFT JOIN t_company_project_rel tcpr ON tp.id = tcpr.project_id
                 LEFT JOIN t_company tc ON tcpr.project_id = tp.id and tc.enabled=1

                 LEFT JOIN t_project_team_rel tptr ON tp.id = tptr.project_id
            AND tptr.enabled = 1
                 LEFT JOIN t_team tt ON tptr.team_id = tt.id
            AND tt.enabled = 1
                 LEFT JOIN t_team_worker_rel ttwr ON ttwr.team_id = tt.id
            AND ttwr.enabled = 1
                 LEFT JOIN t_worker tw ON ttwr.worker_id = tw.id
            AND tw.enabled = 1
                 LEFT JOIN t_worker_attendance twa ON tw.id = twa.worker_id
            AND twa.enabled = 1
            AND date_format(twa.attendance_time, '%Y-%m-%d') = CURRENT_DATE
            WHERE  tp.enabled = 1
order by tp.last_modified_time desc

3.第三种就是把所有单独要查询出来的字段放到group by 里面

SELECT
	tc.company_name AS companyName,
	tp.project_name AS projectName,
	tp.project_leader_id AS projectLeaderId,
	tp.id AS projectId,
	count( DISTINCT ( CASE WHEN tw.worker_status = 1 THEN tw.id END ) ) AS workerNum,
	count( DISTINCT ( twa.worker_id ) ) AS attendanceNum 
FROM
	t_project tp
	LEFT JOIN t_company_project_rel tcpr ON tp.id = tcpr.project_id
	LEFT JOIN t_company tc ON tcpr.project_id = tp.id 
	AND tc.enabled = 1
	LEFT JOIN t_project_team_rel tptr ON tp.id = tptr.project_id 
	AND tptr.enabled = 1
	LEFT JOIN t_team tt ON tptr.team_id = tt.id 
	AND tt.enabled = 1
	LEFT JOIN t_team_worker_rel ttwr ON ttwr.team_id = tt.id 
	AND ttwr.enabled = 1
	LEFT JOIN t_worker tw ON ttwr.worker_id = tw.id 
	AND tw.enabled = 1
	LEFT JOIN t_worker_attendance twa ON tw.id = twa.worker_id 
	AND twa.enabled = 1 
	AND date_format( twa.attendance_time, '%Y-%m-%d' ) = CURRENT_DATE 
WHERE
	tp.enabled = 1 
GROUP BY
	companyName,
	projectId 
ORDER BY
	tp.last_modified_time DESC

一般像hive,starrocks,clickhouse都会有相同类似情况,都可以采用any_value()函数处理

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/Wind___Drift/article/details/134236610

MySQL 5.7报"this is incompatible with sql_mode=only_full_group_by"错误的解决方案-爱代码爱编程

解决方案一: 修改mysql配置文件,通过手动添加sql_mode的方式强制指定不需要ONLY_FULL_GROUP_BY属性,    在my.ini末尾添加如下: sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_

mysql 报错“this is incompatible with sql_mode=only_full_group_by“-爱代码爱编程

 本次使用的软件版本如下 linux - centos7.6 mysql 5.7.27 一、原理层面        这个错误发生在mysql 5.7 版本及以上版本会出现的问题:        mysql 5.7版本默认的sql配置是:sql_mode="ONLY_FULL_GROUP_BY",这个配置严格执行了"SQL92标准"。

mysql8.0 this is incompatible with sql_mode=only_full_group_by-爱代码爱编程

错误日志部分: which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 查看sql_mode SQL语句: SELECT @@SESSION.sql_mod

MySQL- this is incompatible with sql_mode=only_full_group_by错误解决方案-爱代码爱编程

记录一下,我本机电脑 mysql版本是8.0.19 差不多算比较新一个版本吧。 查询数据库版本语句:select VERSION() 就是在使用的 查询sql的 GROUP BY函数的时候就会出现以下报错    Expression #1 of SELECT list is not in GROUP BY clause and contain

mysql错误-this is incompatible with sql-爱代码爱编程

原因分析: 一、原理层面 这个错误发生在mysql 5.7.5 版本及以上版本会出现的问题: mysql 5.7.5版本以上默认的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”,这个配置严格执行了"SQL92标准"。 很多从5.6升级到5.7时,为了语法兼容,大部分都会选择调整sql_mode,使其保持跟5.6一致,为了尽量兼容

【mysql】异常报错解决 “this is incompatible with sql-爱代码爱编程

项目场景: 将jar包部署至linix运行,运行时提示 :  com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException 异常报错 问题描述  MySQLSyntaxErrorException 异常报错内容如下: com.mysql.jdbc.exceptions.jdbc4.MySQL

mysql错误-爱代码爱编程

项目场景 有时候,遇到数据库重复数据,需要将数据进行分组,并取出其中一条来展示,这时就需要用到group by语句。 但是,如果mysql是高版本,当执行group by时,select的字段不属于group by的字段的