MySQL公共表表达式(CTE)语法报错求助——HackerRank面试题相关

我正在解决HackerRank的面试SQL题，直接执行以下聚合查询可正常运行：

SELECT challenge_id, SUM(total_submissions) AS cid_tot_sub, SUM(total_accepted_submissions) AS cid_tot_acc_sub
      FROM Submission_Stats
      GROUP BY challenge_id

但使用MySQL公共表表达式(CTE)时出现如下语法错误：

ERROR 1064 (42000) at line 55: 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 'cte_ss AS (
SELECT challenge_id, SUM(total_submissions) AS cid_tot_sub, SU' at line 2

我使用的CTE代码如下：

WITH 
    cte_ss AS (
      SELECT challenge_id, SUM(total_submissions) AS cid_tot_sub, SUM(total_accepted_submissions) AS cid_tot_acc_sub
      FROM Submission_Stats
      GROUP BY challenge_id
    ),
    cte_vs AS (
      SELECT challenge_id, SUM(total_views) AS cid_tot_views, SUM(total_unique_views) AS cid_tot_uniq_views
      FROM View_Stats
      GROUP BY challenge_id
    )
select * from cte_ss;

问题原因 #

MySQL从8.0版本才开始支持CTE（WITH子句），如果你的MySQL服务器版本低于8.0，就会触发这个语法错误——低版本MySQL无法识别WITH关键字。

解决方案 #

方案1：升级MySQL版本 #

将MySQL升级到8.0及以上版本，即可正常使用CTE语法。

方案2：用子查询替代CTE #

如果无法升级版本，可以把CTE改写为子查询，示例代码如下：

SELECT * 
FROM (
    SELECT challenge_id, SUM(total_submissions) AS cid_tot_sub, SUM(total_accepted_submissions) AS cid_tot_acc_sub
    FROM Submission_Stats
    GROUP BY challenge_id
) AS cte_ss;

如果需要关联两个统计结果，可使用子查询关联：

SELECT 
    s.challenge_id,
    s.cid_tot_sub,
    s.cid_tot_acc_sub,
    v.cid_tot_views,
    v.cid_tot_uniq_views
FROM (
    SELECT challenge_id, SUM(total_submissions) AS cid_tot_sub, SUM(total_accepted_submissions) AS cid_tot_acc_sub
    FROM Submission_Stats
    GROUP BY challenge_id
) AS s
LEFT JOIN (
    SELECT challenge_id, SUM(total_views) AS cid_tot_views, SUM(total_unique_views) AS cid_tot_uniq_views
    FROM View_Stats
    GROUP BY challenge_id
) AS v ON s.challenge_id = v.challenge_id;

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