MariaDB递归查询WITH子句语法错误排查求助

你的SQL语法错误根源在于MariaDB不允许在递归CTE的递归分支中嵌套WITH子句，同时原SQL的括号使用和子查询结构也存在冗余问题。

错误点拆解 #

• 递归CTE的UNION ALL右侧分支里嵌套了WITH works AS (...)，这不符合MariaDB的CTE语法规范。
• 子查询外层的冗余括号（比如(authors as ath)外层的括号）虽不触发报错，但会降低代码可读性。

修正方案 #

把嵌套的works逻辑合并到递归分支中，用JOIN替代多层子查询，简化结构：

WITH RECURSIVE
authors AS (
    SELECT dblpkey, author FROM inproau
    UNION ALL
    SELECT dblpkey, author FROM artiau
),
collegues(collegue) AS (
    SELECT 'Paul Erdös'
    UNION ALL
    SELECT DISTINCT a.author
    FROM authors a
    JOIN authors b ON a.dblpkey = b.dblpkey
    WHERE b.author IN (SELECT collegue FROM collegues)
      AND a.author NOT IN (SELECT collegue FROM collegues) -- 避免循环重复
)
SELECT * FROM collegues;

补充说明 #

• 新增AND a.author NOT IN (SELECT collegue FROM collegues)是为了防止递归过程中重复获取已存在的作者，避免无限递归。
• 用JOIN关联同一作者表，替代原有的多层子查询，逻辑更清晰，性能也更优。

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