如何在分类树中获取父类、兄弟类与子类？求助WITH RECURSIVE查询实现

首先，我们需要一个存储分类层级关系的数据库表，典型结构如下：

CREATE TABLE category (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL COMMENT '分类名称',
    parent_id INT DEFAULT NULL COMMENT '父分类ID，顶级分类为NULL',
    level INT NOT NULL COMMENT '分类层级，比如1、2、3',
    FOREIGN KEY (parent_id) REFERENCES category(id)
);

根据你给出的分类结构，插入对应数据（用具体ID映射层级关系）：

-- 插入Level 1分类
INSERT INTO category (name, parent_id, level) VALUES 
('Level 1 category 1', NULL, 1),
('Level 1 category 2', NULL, 1);

-- 插入Level 2分类
INSERT INTO category (name, parent_id, level) VALUES 
('Level 2 category 1', 1, 2),
('Level 2 category 2', 1, 2),
('Level 2 category 3', 2, 2);

-- 插入Level 3分类
INSERT INTO category (name, parent_id, level) VALUES 
('Level 3 category 1', 3, 3),
('Level 3 category 2', 3, 3),
('Level 3 category 3', 3, 3),
('Level 3 category 4', 4, 3),
('Level 3 category 5', 4, 3),
('Level 3 category 6', 4, 3),
('Level 3 category 7', 5, 3);

下面针对你的需求，分模块实现查询：

一、获取指定分类的父类 #

1. 获取直接父类（对应你例子中的父类定义） #

直接通过parent_id关联查询即可：

比如查询Level 3 category 5的直接父类：

SELECT c2.name AS parent_category
FROM category c1
JOIN category c2 ON c1.parent_id = c2.id
WHERE c1.name = 'Level 3 category 5';

结果会返回Level 2 category 2，符合你的例子。

2. 获取所有祖先父类（递归查询全层级父类） #

使用WITH RECURSIVE递归遍历所有上层分类：

WITH RECURSIVE ancestor AS (
    -- 初始节点：指定分类的直接父类
    SELECT c.id, c.name, c.parent_id
    FROM category c
    JOIN category target ON c.id = target.parent_id
    WHERE target.name = 'Level 3 category 5'
    UNION ALL
    -- 递归向上遍历父类
    SELECT c.id, c.name, c.parent_id
    FROM category c
    JOIN ancestor a ON c.id = a.parent_id
)
SELECT name AS ancestor_category FROM ancestor;

结果会返回Level 2 category 2和Level 1 category 1。

二、获取指定分类的兄弟类 #

根据你的例子，兄弟类分两种场景实现：

1. 狭义兄弟类（同父类的同级分类，排除自身） #

对应你第一个例子中Level 3 category 5的兄弟类（Level 3 category 4、Level 3 category 6）：

SELECT name AS sibling_category
FROM category
WHERE parent_id = (SELECT parent_id FROM category WHERE name = 'Level 3 category 5')
AND name != 'Level 3 category 5';

2. 广义同级类（同一层级的所有分类，排除自身） #

对应你第二个例子中Level 2 category 3的兄弟类（Level 2 category 1、Level 2 category 2）：

SELECT name AS same_level_category
FROM category
WHERE level = (SELECT level FROM category WHERE name = 'Level 2 category 3')
AND name != 'Level 2 category 3';

三、获取指定分类的子类 #

使用WITH RECURSIVE递归查询所有子类（包括直接子类和多级后代）：

比如查询Level 2 category 3的所有子类：

WITH RECURSIVE descendant AS (
    -- 初始节点：指定分类的直接子类
    SELECT id, name, parent_id
    FROM category
    WHERE parent_id = (SELECT id FROM category WHERE name = 'Level 2 category 3')
    UNION ALL
    -- 递归向下遍历后代子类
    SELECT c.id, c.name, c.parent_id
    FROM category c
    JOIN descendant d ON c.parent_id = d.id
)
SELECT name AS descendant_category FROM descendant;

结果会返回Level 3 category 7，符合你的例子。

如果只需要直接子类，无需递归，直接查询即可：

SELECT name AS direct_child_category
FROM category
WHERE parent_id = (SELECT id FROM category WHERE name = 'Level 2 category 3');

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