MySQL LEFT JOIN语法错误排查：我的语句哪里出问题了？

错误信息 #

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN rep.de_para_game as b on b.exe = a.name' at line 14

原执行SQL语句 #

drop table if exists rep.report_daily_gaming;
create table rep.report_daily_gaming as 
select 
    date(a.date) as date,
    a.name,
    b.alias,
    max(a.cpu) as cpu,
    max(a.mem) as mem,
    max(a.gpu_clock) as gpu_clock,
    max(a.gpu_temp) as gpu_temp,
    max(a.gpu_load) as gpu_load,
    max(a.cpu_load) as cpu_load,
    max(a.ram_load) as ram_load,
    count(a.id) as minutes
from infos.gaming as a 
group by a.date, a.name
LEFT JOIN rep.de_para_game as b
on b.exe = a.name

表结构信息 #

infos.gaming表 #

Name: infos.gaming
cpu double
cpu_load double
date datetime
gpu_clock double
gpu_load double
gpu_temp double
id mediumint
mem bigint
name varchar(255)
ram_load double
screen tinyint(1)

rep.de_para_game表 #

Name: rep.de_para_game
alias varchar(255)
exe varchar(255)
id mediumint

MySQL版本 #

mysql> SHOW VARIABLES LIKE 'version';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 8.0.30 |
+---------------+--------+

错误原因及修正 #

核心错误：JOIN子句位置错误 #

SQL语法规则中，JOIN子句必须放在GROUP BY子句之前，原SQL把GROUP BY写在了LEFT JOIN前面，这是触发语法错误的直接原因。

次要问题：GROUP BY字段不完整 #

MySQL 8.0默认开启ONLY_FULL_GROUP_BY模式，要求SELECT中的非聚合字段必须出现在GROUP BY列表中。原SQL中SELECT了b.alias，但GROUP BY里未包含该字段，执行时会触发额外的语法错误。由于rep.de_para_game中exe和alias是一一对应的，我们可以用聚合函数包裹b.alias，或者直接将其加入GROUP BY。

修正后的SQL（方案一：用聚合函数规避） #

drop table if exists rep.report_daily_gaming;
create table rep.report_daily_gaming as 
select 
    date(a.date) as date,
    a.name,
    MAX(b.alias) as alias, -- 用MAX确保符合ONLY_FULL_GROUP_BY要求
    max(a.cpu) as cpu,
    max(a.mem) as mem,
    max(a.gpu_clock) as gpu_clock,
    max(a.gpu_temp) as gpu_temp,
    max(a.gpu_load) as gpu_load,
    max(a.cpu_load) as cpu_load,
    max(a.ram_load) as ram_load,
    count(a.id) as minutes
from infos.gaming as a 
LEFT JOIN rep.de_para_game as b
    on b.exe = a.name -- 调整JOIN到GROUP BY之前
group by date(a.date), a.name; -- 与SELECT中的date字段保持一致

修正后的SQL（方案二：将alias加入GROUP BY） #

drop table if exists rep.report_daily_gaming;
create table rep.report_daily_gaming as 
select 
    date(a.date) as date,
    a.name,
    b.alias,
    max(a.cpu) as cpu,
    max(a.mem) as mem,
    max(a.gpu_clock) as gpu_clock,
    max(a.gpu_temp) as gpu_temp,
    max(a.gpu_load) as gpu_load,
    max(a.cpu_load) as cpu_load,
    max(a.ram_load) as ram_load,
    count(a.id) as minutes
from infos.gaming as a 
LEFT JOIN rep.de_para_game as b
    on b.exe = a.name
group by date(a.date), a.name, b.alias;

内容的提问来源于stack exchange，提问作者Rillis Vernucio