mysql 查询报错 this is incompatible with sql_mode=only_full_group_by

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()函数处理

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值