MySQL中使用WITH与CTEs解决HackerRank题目时语法错误排查

问题背景 #

在解决面试题时，尝试用MySQL的WITH CTE语法编写查询，执行后触发语法错误，错误提示：

ERROR 1064 (42000) at line 4: 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 'contests AS (
SELECT ct.contest_id, ct.hacker_id, ct.name, co.college_id
' at line 1

原代码如下：

WITH contests AS (
  SELECT ct.contest_id, ct.hacker_id, ct.name, co.college_id
  FROM Contests ct
  LEFT JOIN Colleges co ON ct.contest_id = co.contest_id
  LEFT JOIN Challenges ch ON co.college_id = ch.college_id
),
Stats AS (
  SELECT 
    vs.challenge_id, 
    SUM(vs.total_views) as tv, 
    SUM(vs.total_unique_views) as tuv, 
    SUM(ss.total_submissions) as ts,
    SUM(ss.total_accepted_submissions) as tas
  FROM View_Stats vs
  JOIN Submission_Stats ss on vs.challenge_id = ss.challenge_id
  GROUP BY vs.challenge_id
)
SELECT contest_id, hacker_id, name, tv, tuv, ts, tas
FROM contests
JOIN Stats ON contests.challenge_id = Stats.challenge_id
HAVING SUM(tv + tuv + ts + tas) > 0;

错误原因与修正要点 #

1. 语法错误核心原因：MySQL 8.0及以上版本才支持WITH CTE语法。如果你的MySQL版本低于8.0，要么升级版本，要么改用子查询替代CTE。若版本符合要求，原代码还存在以下逻辑漏洞：
   • contests CTE未选择challenge_id字段，但后续JOIN操作依赖该字段，必须添加到SELECT列表；
   • Stats中使用JOIN会丢失仅存在浏览数据或仅存在提交数据的挑战，需改用能覆盖所有情况的合并方式；
   • 最终查询的HAVING用法错误——SELECT中无聚合函数，应先按contest级别汇总统计数据，再过滤总数据大于0的记录。

修正后的完整代码 #

WITH contests AS (
  -- 补充关联所需的challenge_id字段
  SELECT ct.contest_id, ct.hacker_id, ct.name, ch.challenge_id
  FROM Contests ct
  LEFT JOIN Colleges co ON ct.contest_id = co.contest_id
  LEFT JOIN Challenges ch ON co.college_id = ch.college_id
),
view_stats_agg AS (
  -- 单独汇总浏览统计
  SELECT 
    challenge_id,
    SUM(total_views) AS tv,
    SUM(total_unique_views) AS tuv
  FROM View_Stats
  GROUP BY challenge_id
),
submission_stats_agg AS (
  -- 单独汇总提交统计
  SELECT 
    challenge_id,
    SUM(total_submissions) AS ts,
    SUM(total_accepted_submissions) AS tas
  FROM Submission_Stats
  GROUP BY challenge_id
),
stats AS (
  -- 合并两类统计，处理单边存在数据的情况（模拟FULL JOIN）
  SELECT 
    COALESCE(vs.challenge_id, ss.challenge_id) AS challenge_id,
    COALESCE(vs.tv, 0) AS tv,
    COALESCE(vs.tuv, 0) AS tuv,
    COALESCE(ss.ts, 0) AS ts,
    COALESCE(ss.tas, 0) AS tas
  FROM view_stats_agg vs
  LEFT JOIN submission_stats_agg ss ON vs.challenge_id = ss.challenge_id
  UNION
  SELECT 
    COALESCE(ss.challenge_id, vs.challenge_id) AS challenge_id,
    COALESCE(vs.tv, 0) AS tv,
    COALESCE(vs.tuv, 0) AS tuv,
    COALESCE(ss.ts, 0) AS ts,
    COALESCE(ss.tas, 0) AS tas
  FROM submission_stats_agg ss
  LEFT JOIN view_stats_agg vs ON ss.challenge_id = vs.challenge_id
  WHERE vs.challenge_id IS NULL
),
contest_stats AS (
  -- 按赛事级别汇总所有统计数据
  SELECT 
    c.contest_id,
    c.hacker_id,
    c.name,
    SUM(s.tv) AS total_views,
    SUM(s.tuv) AS total_unique_views,
    SUM(s.ts) AS total_submissions,
    SUM(s.tas) AS total_accepted_submissions
  FROM contests c
  LEFT JOIN stats s ON c.challenge_id = s.challenge_id
  GROUP BY c.contest_id, c.hacker_id, c.name
)
-- 过滤无任何统计数据的赛事
SELECT 
  contest_id,
  hacker_id,
  name,
  total_views,
  total_unique_views,
  total_submissions,
  total_accepted_submissions
FROM contest_stats
WHERE total_views + total_unique_views + total_submissions + total_accepted_submissions > 0;

补充说明 #

• 若使用MySQL 5.x版本，需将所有CTE替换为嵌套子查询；
• 代码通过拆分统计、合并缺失数据、按赛事汇总三个环节，确保所有有效数据被计算，同时排除无任何交互记录的赛事。

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