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