使用WITH AS语法查询4列时出现1064语法错误求助

你的代码有两个关键问题导致错误码1064：

• WITH AS定义CTE后必须跟上实际查询逻辑：CTE只是临时表的定义，不能单独以分号结尾，必须在后面写查询语句调用这个临时表
• MySQL版本兼容问题：如果你的MySQL是5.7或更早版本，本身不支持WITH AS语法，得用子查询替代

针对MySQL 8.0及以上版本的修复代码 #

WITH counter_table(s_date, h_id, h_name, counter) AS (
    SELECT s.submission_date, s.hacker_id, h.name, COUNT(DISTINCT s.submission_id)
    FROM submissions s
    INNER JOIN hackers h ON h.hacker_id = s.hacker_id
    GROUP BY s.submission_date, s.hacker_id, h.name
)
SELECT * FROM counter_table; -- 必须添加这行来查询CTE的数据

针对MySQL 5.7及更早版本的替代方案（用子查询） #

SELECT * FROM (
    SELECT s.submission_date AS s_date, s.hacker_id AS h_id, h.name AS h_name, COUNT(DISTINCT s.submission_id) AS counter
    FROM submissions s
    INNER JOIN hackers h ON h.hacker_id = s.hacker_id
    GROUP BY s.submission_date, s.hacker_id, h.name
) AS counter_table;

额外提示：如果submission_id是表的主键或唯一值，COUNT(DISTINCT s.submission_id)可以简化为COUNT(s.submission_id)或COUNT(*)，查询效率会更高。

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