如何按type字段筛选符合条件的父子分类并输出对应层级路径

从menu分类表中筛选type=1的父子分类，规则如下：

• 若父/子节点type不等于1则不在结果中展示，但其下type等于1的子节点仍需正常出现在结果中
• 最终输出仅包含type=1节点的完整分类路径

MySQL 8.0+ 通用递归写法（支持任意层级分类） #

该方案不受分类层级限制，不管分类有多少层都可以正确生成路径：

WITH RECURSIVE category_path AS (
    -- 锚点：筛选所有type=1的节点，初始路径为节点自身名称
    SELECT id, name, CAST(name AS CHAR(200)) AS full_path, main_cat
    FROM menu
    WHERE type = 1
    UNION ALL
    -- 递归向上查找type=1的父节点，拼接到路径最前端
    SELECT cp.id, cp.name, CONCAT(m.name, ' -> ', cp.full_path), m.main_cat
    FROM category_path cp
    INNER JOIN menu m ON cp.main_cat = m.id
    WHERE m.type = 1
)
-- 取每个节点的最长完整路径，去重排序
SELECT DISTINCT full_path 
FROM category_path t
WHERE NOT EXISTS (
    SELECT 1 FROM category_path 
    WHERE id = t.id AND LENGTH(full_path) > LENGTH(t.full_path)
)
ORDER BY full_path;

低版本MySQL 固定3层分类写法 #

如果你的分类层级固定为最多3层，可以基于你原有的左连接逻辑改造：

SELECT CONCAT_WS(' -> ', 
    CASE WHEN m1.type=1 THEN m1.name END,
    CASE WHEN m2.type=1 THEN m2.name END,
    CASE WHEN m3.type=1 THEN m3.name END
) AS full_path
FROM menu m3
LEFT JOIN menu m2 ON m3.main_cat = m2.id
LEFT JOIN menu m1 ON m2.main_cat = m1.id
WHERE m3.type = 1
UNION
SELECT CONCAT_WS(' -> ', 
    CASE WHEN m1.type=1 THEN m1.name END,
    CASE WHEN m2.type=1 THEN m2.name END
) AS full_path
FROM menu m2
LEFT JOIN menu m1 ON m2.main_cat = m1.id
WHERE m2.type = 1 AND NOT EXISTS (SELECT 1 FROM menu m3 WHERE m3.main_cat = m2.id AND m3.type=1)
ORDER BY full_path;

两种写法执行后均会得到符合规则的结果：

LED -> QLED
LED -> QLED -> 24inc
PC -> HP
Phone -> LG -> A100
Phone -> LG -> C300

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