MySQL WITH RECURSIVE查询未生效，求递归拼接分类路径方案

表结构说明 #

• 课程表 mdl_course：id, fullname, shortname, category
• 课程分类表 mdl_course_categories：id, name, parent

需求 #

编写SELECT语句，将课程所属分类从叶子节点到ID为0的顶级父节点的名称拼接成完整路径。

尝试的代码 #

我用WITH RECURSIVE写了如下查询，但执行后只显示叶子节点分类，没有完成递归拼接：

with recursive
n as (
  select mc.id, mc.fullname , mc.shortname , mcc.name  as category, mcc.parent 
  from mdl_course mc
  join mdl_course_categories mcc on mcc.id = mc.category 
 union all
  select n.id, n.fullname, n.shortname, concat(n.category, ' - ', mcc.name) as category , mcc.parent 
  from n
  join mdl_course_categories mcc on mcc.id  = n.parent
)
select id, fullname, shortname, category from n

补充建表语句 #

课程表 #

CREATE TABLE `mdl_course` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `category` bigint(20) NOT NULL DEFAULT 0,
  `sortorder` bigint(20) NOT NULL DEFAULT 0,
  `fullname` varchar(254) NOT NULL DEFAULT '',
  `shortname` varchar(255) NOT NULL DEFAULT ''
);

课程分类表 #

CREATE TABLE `mdl_course_categories` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `idnumber` varchar(100) DEFAULT NULL,
  `description` longtext DEFAULT NULL,
  `descriptionformat` tinyint(4) NOT NULL DEFAULT 0,
  `parent` bigint(20) NOT NULL DEFAULT 0
);

解决方案 #

问题出在递归终止条件和最终结果的筛选上：当前查询返回了所有递归层级的记录，但没有筛选出每个课程的最终完整路径（即递归到parent=0的那条记录），同时缺少明确的递归终止判断。

修改后的查询如下：

WITH RECURSIVE category_path AS (
    -- 初始查询：获取课程关联的叶子分类
    SELECT 
        mc.id AS course_id,
        mc.fullname,
        mc.shortname,
        mcc.id AS category_id,
        mcc.name AS category_name,
        mcc.parent
    FROM mdl_course mc
    JOIN mdl_course_categories mcc ON mcc.id = mc.category
    
    UNION ALL
    
    -- 递归查询：向上拼接父分类名称
    SELECT 
        cp.course_id,
        cp.fullname,
        cp.shortname,
        mcc.id AS category_id,
        CONCAT(cp.category_name, ' - ', mcc.name) AS category_name,
        mcc.parent
    FROM category_path cp
    JOIN mdl_course_categories mcc ON mcc.id = cp.parent
    -- 终止条件：父节点不为0时才继续递归
    WHERE cp.parent != 0
)
-- 筛选每个课程的最终完整路径（即父节点为0的记录）
SELECT 
    course_id AS id,
    fullname,
    shortname,
    category_name AS category
FROM category_path
WHERE parent = 0;

关键调整说明 #

1. 增加WHERE cp.parent != 0的终止条件，避免递归到顶级节点后继续无效循环
2. 最终查询仅筛选parent=0的记录，这就是每个课程从叶子到顶级的完整分类路径
3. 重命名CTE别名，让逻辑层级更清晰

如果你的顶级分类父节点不是0，只需对应调整WHERE条件中的数值即可。

内容的提问来源于stack exchange，提问作者Amira Elsayed Ismail