MySQL中CTE语法报错排查：HackerRank竞赛排行榜SQL问题

问题背景 #

需求为：计算每个黑客所有挑战的最高得分之和作为总得分，输出hacker_id、name、总得分，按总得分降序排序，得分相同则按hacker_id升序，排除总得分0的黑客。

原SQL代码（在MS SQL Server中可正常运行）：

With max_score as (
select max(score) as mscore, hacker_id, challenge_id
from submissions
group by hacker_id, challenge_id
)
select h.hacker_id, h.name, sum(ms.mscore) as total_score
from hackers h join max_score ms on ms.hacker_id = h.hacker_id
group by h.hacker_id, h.name
having sum(ms.mscore)>0
order by sum(ms.mscore) desc, hacker_id asc;

报错信息 #

ERROR 1064 (42000) at line 1:
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 'maxscore as (
select max(score) as mscore, hacker_id, challenge_id
from submissi' at line 1

报错原因及修复 #

原因 #

1. 版本兼容问题：MySQL 8.0之前的版本不支持WITH关键字定义的CTE（公共表表达式），若你使用的MySQL版本低于8.0，会直接无法识别该语法导致报错。
2. 拼写失误：报错信息指向maxscore，说明你实际执行时可能把CTE名称max_score的下划线遗漏，写成了maxscore，这也会触发语法错误。

修复方案 #

方案1：兼容所有MySQL版本（用子查询替代CTE）

将CTE改写为内联子查询，避免版本限制：

select h.hacker_id, h.name, sum(ms.mscore) as total_score
from hackers h 
join (
    select max(score) as mscore, hacker_id, challenge_id
    from submissions
    group by hacker_id, challenge_id
) ms on ms.hacker_id = h.hacker_id
group by h.hacker_id, h.name
having sum(ms.mscore) > 0
order by sum(ms.mscore) desc, hacker_id asc;

方案2：适配MySQL 8.0+版本

若你的MySQL版本是8.0及以上，只需修正拼写并统一关键字大小写（MySQL对关键字大小写敏感取决于系统配置，建议用小写with）：

with max_score as (
select max(score) as mscore, hacker_id, challenge_id
from submissions
group by hacker_id, challenge_id
)
select h.hacker_id, h.name, sum(ms.mscore) as total_score
from hackers h join max_score ms on ms.hacker_id = h.hacker_id
group by h.hacker_id, h.name
having sum(ms.mscore) > 0
order by sum(ms.mscore) desc, hacker_id asc;

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